Invalid use side-effecting operator Insert within a function

Question

I have the following code in my sql function   if  max chi  gt  -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

User · Answer

Functions cannot be used to modify base table information  use a stored procedure

User · Answer

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

User · Answer

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

User · Answer

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

[sql] Invalid use side-effecting operator Insert within a function

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008