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?
4 Answers
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.
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
Functions cannot be used to modify base table information, use a stored procedure.
There is an exception (I'm using SQL 2014) when you are only using Insert/Update/Delete on Declared-Tables. These Insert/Update/Delete statements cannot contain an OUTPUT statement. The other restriction is that you are not allowed to do a MERGE, even into a Declared-Table. I broke up my Merge statements, that didn't work, into Insert/Update/Delete statements that did work.
The reason I didn't convert it to a stored-procedure is that the table-function was faster (even without the MERGE) than the stored-procedure. This is despite the stored-procedure allowing me to use Temp-Tables that have statistics. I needed the table-function to be very fast, since it is called 20-K times/day. This table function never updates the database.
I also noticed that the NewId() and RAND() SQL functions are not allowed in a function.
