Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
share|improve this question
    
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. –  Aaron Bertrand Feb 9 '12 at 21:05
add comment

1 Answer 1

up vote 5 down vote accepted

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
share|improve this answer
    
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 –  CoffeeAddict Feb 9 '12 at 20:53
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. –  Aaron Bertrand Feb 9 '12 at 21:05
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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