Insert/Update/Delete with function in SQL Server

No, you cannot.

From SQL Server Books Online:

User-defined functions cannot be used to perform actions that modify the database state.

Ref.


Functions in SQL Server, as in mathematics, can not be used to modify the database. They are intended to be read only and can help developer to implement command-query separation. In other words, asking a question should not change the answer. When your program needs to modify the database use a stored procedure instead.


Yes, you can!))

Disclaimer: This is not a solution, it is more of a hack to test out something. User-defined functions cannot be used to perform actions that modify the database state.

I found one way to make INSERT, UPDATE or DELETE in function using xp_cmdshell.

So you need just to replace the code inside @sql variable.

CREATE FUNCTION [dbo].[_tmp_func](@orderID NVARCHAR(50))
RETURNS INT
AS
BEGIN
DECLARE @sql varchar(4000), @cmd varchar(4000)
SELECT @sql = 'INSERT INTO _ord (ord_Code) VALUES (''' + @orderID + ''') '
SELECT @cmd = 'sqlcmd -S ' + @@servername +
              ' -d ' + db_name() + ' -Q "' + @sql + '"'
EXEC master..xp_cmdshell @cmd, 'no_output'
RETURN 1
END

You can't update tables from a function like you would a stored procedure, but you CAN update table variables.

So for example, you can't do this in your function:

create table MyTable
(
    ID int,
    column1 varchar(100)
)
update [MyTable]
set column1='My value'

but you can do:

declare @myTable table
(
    ID int,
    column1 varchar(100)
)

Update @myTable
set column1='My value'