Invalid use side-effecting operator Insert within a function
sql, sql-server, sql-server-2008
Solution
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.
Problem
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?