Invalid use side-effecting operator Insert within a function

Question

I have the following code in my sql function:

if @max_chi > -999 begin     INSERT INTO CH_TABLE(X1, X2, VALUE)     VALUES(cur_out.sessionnumber, maxpos, max_chi)      commit end 

The following is a SQL Server 2008 Query and it gives me an error:

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

Why am I not allowed to do this? What can I do to fix this?

Answer 1

You can't use a function to insert data into a base table. Functions return data. This is listed as the very first limitation in the documentation:

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

"Modify the database state" includes changing any data in the database (though a table variable is an obvious exception the OP wouldn't have cared about 3 years ago - this table variable only lives for the duration of the function call and does not affect the underlying tables in any way).

You should be using a stored procedure, not a function.

Answer 2

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 or update using sqlcmd.exe 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