5

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)
1
  • 2
    You should be using a stored procedure, not a function. A function in SQL Server is not like a method in OO languages - it is meant merely to return data to the caller. Commented Feb 9, 2012 at 21:05

1 Answer 1

11

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
2
  • well this is for maintenance purposes so what is the hack? So you're saying I'm better off just to create a main stored proc (lets say it's called CreateUserForMaintenance) and have it call these inserts after I throw some of those insert Commented Feb 9, 2012 at 20:53
  • 5
    You should be using a stored procedure, not a function. A function in SQL Server is not like a method in OO languages - it is meant merely to return data to the caller. Commented Feb 9, 2012 at 21:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.