Invalid use of a side-effecting operator 'INSERT' within a function - Multiple Inserts in Function

Question

If this is not the right forum please let me know and move it for me.

I've just got a bunch of inserts like this toward the end of my scalar function:

   INSERT INTO [Raptor].[dbo].[UserRole]
           ([RoleId]
           ,[UserId])
     VALUES
           (3
           ,@NewUserID)

    INSERT INTO [Raptor].[dbo].[UserRole]
               ([RoleId]
               ,[UserId])
         VALUES
               (5
               ,@NewUserID)

Answer 1

In T-SQL, you cannot modify any data in a function. There is no straightforward way around it. There are some obscure hacks, but I would not use them. Use a stored procedure.

The hack, quoting from Erland Sommarskog:

CREATE FUNCTION loophole(@i int) RETURNS varchar(20) AS
  BEGIN
     DECLARE @sql varchar(MAX),
             @cmd varchar(4000)
     SELECT @sql = ' UPDATE rsci ' +
                   ' SET b = CASE ' + ltrim(str(@i + 1)) +
                   ' WHEN 1 THEN ''Ett'' WHEN 2 THEN ''Två''' +
                   ' WHEN 3 THEN ''Tre'' WHEN 4 THEN ''Fyra''' +
                   ' WHEN 5 THEN ''Fem'' WHEN 6 THEN ''Sex''' +
                   ' WHEN 7 THEN ''Sju'' WHEN 8 THEN ''Åtta''' +
                   ' WHEN 9 THEN ''Nio'' WHEN 10 THEN ''Tio'' END' +
                   ' WHERE a = ' + ltrim(str(@i + 1))
     SELECT @cmd = 'sqlcmd -S ' + @@servername + ' -d ' + db_name() +
                   ' -Q "' + @sql + '"'
     EXEC master..xp_cmdshell @cmd, 'no_output'
     RETURN (SELECT b FROM rsci WHERE a = @i)
  END

Answer 2

Few months ago I did a presentation in our local SQL Server user group about user defined function. I also showed 2 ways that we can modify data from a user defined function, but also the catastrophic results that it can have. The code that I've added shows why we shouldn't modify data from a function even if we found a way to do it.

--Trying to create a UDF that modifies data
--First I'll create a table
IF OBJECT_ID('dbo.DemoTbl') IS NOT NULL
      DROP TABLE dbo.DemoTbl;
go

CREATE TABLE dbo.DemoTbl (ID INT IDENTITY(1,1));
GO

--Creating the function that will insert data into the table
--Need to rename the name of the server and Database in the code
CREATE OR ALTER FUNCTION dbo.MyFunc()
RETURNS INT
AS
BEGIN
      EXEC xp_cmdshell 'OSQL -E -Smylaptop -Q"INSERT INTO StackOverFlow.dbo.DemoTbl DEFAULT VALUES"'; --Rename the DB and server
      RETURN (10);
END
GO

--Check the if the function inserts data into the table
SELECT * FROM dbo.DemoTbl;
SELECT dbo.MyFunc();
SELECT * FROM dbo.DemoTbl;
go

--make sure that we don't have any records in the table
TRUNCATE TABLE dbo.DemoTbl;

--How many records do you expect to get after the rollback?
BEGIN TRAN
      SELECT dbo.MyFunc();
ROLLBACK TRAN
go
SELECT * FROM dbo.DemoTbl;
go

--What are the results that you expect to get from this statement?
--What results do you expect from this query?
SELECT *
FROM (SELECT *, dbo.MyFunc() as num FROM dbo.DemoTbl) a LEFT JOIN dbo.DemoTbl t on a.ID = t.ID;

Answer 3

DECLARE @sql nvarchar(max);
SET @sql = ' INSERT INTO [dbo].[Sequence]  (id,owner_id,no_order) VALUES (  0, ' + @owner_id + @order_new  +' )';
EXEC sys.sp_executesql @sql;