Hi All,

We are not allowed to write delete statement in a function. It will give the below error.

"Invalid use of a side-effecting operator 'DELETE' within a function."

We will get the same error for truncate/drop statements.

What is the reason behind this error?

Regards,

Julie

Because functions are intended to be run in SELECT queries, and it's difficult to select a query plan for a SELECT query if a function it uses changes the database.

If you want to change the database, use a stored procedure.

David

Hello - The reason is to allow Query optimizer to it use freely in query without worrying that side-effecting changes (Such as creating temporary table ) may happen which in turn causes QP to produce changed execution plans.

Imagine if functions were allowed to make such changes then they may produce undesired/ inconsistent results every time they are invoked !

In short, this allows optimizer to prepare consistent plan at all times. We have several other ways and means to perform DML operations such as Stored procedures, batches or even simple T-SQL

Data operations such as deletes, updates, inserts, truncates etc cannot be performed from a Function.  Use Stored Procedures instead.

http://stackoverflow.com/questions/1179758/function-vs-stored-procedure-in-sql-server

Hi All,

We are not allowed to write delete statement in a function. It will give the below error.

"Invalid use of a side-effecting operator 'DELETE' within a function."

We will get the same error for truncate/drop statements.

What is the reason behind this error?

Regards,

Julie

Think of it this way:

SELECT Name, dbo.DROPFUNCTION(Name)
FROM SYS.ALL_OBJECTS 
WHERE type_desc like '%function%'

What would SQL Server do with that statement? I loop over all functions in the database and call a function for each row returned to drop the function. What would happen when the function attempted to drop itself? This is one of many reasons that it is forbidden. :)

I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)

• Edited by Daniel Janik 16 hours 31 minutes ago

Hi All,

We are not allowed to write delete statement in a function. It will give the below error.

"Invalid use of a side-effecting operator 'DELETE' within a function."

We will get the same error for truncate/drop statements.

What is the reason behind this error?

Regards,

Julie

Think of it this way:

SELECT Name, dbo.DROPFUNCTION(Name)
FROM SYS.ALL_OBJECTS 
WHERE type_desc like '%function%'

What would SQL Server do with that statement? I loop over all functions in the database and call a function for each row returned to drop the function. What would happen when the function attempted to drop itself? This is one of many reasons that it is forbidden. :)

I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)

• Edited by Daniel Janik Thursday, June 25, 2015 2:35 PM

SQL Functions are not allowed to have data related side affects, i.e they are not allowed to modify underlying table data. Use a stored procedure

Hi Julie,

The delete/truncate/drop statements in your case have violated the limitation "User-defined functions cannot be used to perform actions that modify the database state".

The fragment from the official BOL for your reference.

Valid Statements in a Function

• DECLARE statements can be used to define data variables and cursors that are local to the function.
• Assignments of values to objects local to the function, such as using SET to assign values to scalar and table local variables.
• And so on
  

Limitations and Restrictions

• User-defined functions cannot be used to perform actions that modify the database state.
• User-defined functions cannot contain an OUTPUT INTO clause that has a table as its target.
• User-defined functions can not return multiple result sets. Use a stored procedure if you need to return multiple result sets.
• And so on

See more details in below links.
Create User-defined Functions
User-Defined Functions

If you have any question, feel free to let me know.

>> We are not allowed to write delete statement in a function. It will give the below error:
"Invalid use of a side-effecting operator 'DELETE' within a function."
We will get the same error for truncate/drop statements. What is the reason behind this error? <<

In functional  and declarative programming languages (not just SQL!) logic disallows side-effects. They would prevent short-cut evaluations, optimizations  and a host of mathematical properties. Like deterministic behavior.  This is usually covered in a freshman course on programming languages. If you want more of the mathematics behind this, read a book on Primitive recursive functions. 

This is why good SQL programmers do not use UDFs.