Sambhashanam
तकनीकी संभाषणम्
Navigation
  • Microsoft Technologies
  • Java Technologies
  • LAMP Technologies
  • Opensource
    • Microsoft Opensource
    • LAMP Opensource
    • Java Opensource
You are here: Home › Microsoft Opensource › Can I write DML statements in SQL server functions?
← Abstract Classes and Pure Virtual Functions
Java Transaction Service (JTS) – The Five Players →

Can I write DML statements in SQL server functions?

November 12, 2013 | Filed under: Microsoft Opensource

The answer of this is “No” you cannot write DML statements in SQL functions.

Functions have Only READ-ONLY database access. If DML operations would be allowed in functions, then functions would be pretty similar to stored procedures. The way it is, a stored procedure can use a function, but not vice versa. Therefore, functions increase the “power” of stored procedures, queries and T-SQL scripts.  UDF-s are also pitched as super-views.

User-defined functions are similar to stored procedures and can be executed as such, or they can be executed in the same fashion as a system function. Like stored procedures, they accept zero or more input parameters, but can return a scalar data type such as int, decimal, varchar, or sql_variant in addition to the new table data type.       The defining structure of a user-defined function is similar to that of a stored procedure. The main differences are the requirement to specify the RETURNS value and to terminate the function with the RETURN statement.       A user-defined function that returns a table data type can declare an internal table variable and return that variable as its return value. These are known as rowset functions, and can be used where table or view expressions are allowed in Transact-SQL queries. This means a table returned by a user-defined function can be referenced in the from clause of a Transact-SQL statement, while stored procedures that return resultsets cannot.       User-defined functions that return a table can be a powerful alternative to views. While views are limited to a single select statement, user-defined functions can contain additional statements that allow more powerful logic than is possible in views.”

Share this:

  • Click to share on Twitter (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Google+ (Opens in new window)

Related

Did you like this article? Share it with your friends!

Tweet

Written by Manish Kumar

Follow me on Twitter
← Abstract Classes and Pure Virtual Functions
Java Transaction Service (JTS) – The Five Players →

One Response to "Can I write DML statements in SQL server functions?"

  1. Sanjib Kumar Barik says:
    September 6, 2015 at 11:24 am

    No we can’t use DML statement with in UDF in sql server.
    All the UDF’s must be deterministic, that is the we cannot use any DML operations in UDF’s.

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Authors

  • Dhananjay Jha
  • Manish Kumar
  • Sushil Pandey

Recent Comments

  • muhammad on Mail merge in java for Microsoft Word document – Part I
  • muhammad on Mail merge in java for Microsoft Word document – Part I
  • Dhananjay Jha on Mail merge in java for Microsoft Word document and convert to PDF without iText – Part II
  • Clyde Symonette on Mail merge in java for Microsoft Word document and convert to PDF without iText – Part II
  • Dhananjay Kumar Jha on Mail merge in java for Microsoft Word document and convert to PDF without iText – Part II

Tag Cloud

.net activiti activiti-spring activiti bpmn C# change sender mail id in wordpress class classloader Conver MS Word to PDF disable user wordpress dox4j glassfish http response compression java Java Mail Merge MS Word javascript confirmation link on image jax-ws on tomcat jax-ws webservice Jee JTS junit junit-spring Link on Image with hidden URL link load spring configuration load spring configuration xml junit maven maven build Merge MS Word using Java Merge Word Document using XdocReport OOPS oracle weblogic PDF conversion using docx4j php mail function sample activiti process with human task and service task sending mail with attachment spring-orm spring framework spring hibernate spring hibernate integration spring jpa spring jpa hibernate integration tomcat jax-ws web service wordpress wordpress sender email

Looking for old post?

© 2021 Sambhashanam