using delete or update command in a function

Question

can we use delete or update command inside a function in sql server 2005 database or any other database?

Answer 1

If you try and use an UPDATE inside a function you'll get:

Server: Msg 443, Level nn, State 1, Procedure function_Name, Line nn
Invalid use of 'UPDATE' within a function.

To avoid this error from happening, make sure that you don’t use an UPDATE statement inside a user-defined function unless it’s updating a local table variable. If you really need to use the UPDATE statement on a table, you have to use a stored procedure instead of a user-defined function for this purpose.

Create Function

Answer 2

No you cannot use any DML statements (INSERT, UPDATE, and DELETE) on base tables. Another problem with a UDF is that SQL functions returning non-deterministic values are not allowed to be called from inside an UDF. Although UDFs have their own advantages they can be used in a Select, Where, or Case statement. Can be used in joins.