Setting a Variable from Dynamic SQL

Q: Pop Rivett, how do I set variables from dynamically-executed SQL that I can then access?‘

Pop: Bless you, my boy, for asking. Setting a variable within dynamic SQL in Transact SQL is a much more common problem than you might think. From looking at the documentation, you might be led to believe that the only way to pass back results from dynamically-executed SQL is via a result set. But to my way of thinking, where one just wants one or more values in variables, this makes for tedious processing. However, fear not. There is a way of doing it using sp_ExecuteSQL that, for some reason, was never documented in Books Online. Such an omission would never have happened in my day, laddie, I can tell you…

Anyway, I digress. Here is my solution its minimal form. In the list of parameters that you pass to sp_ExecuteSQL, you declare an output variable for values you want passed back to the calling routine, and supply the variable to which to pass it in the parameter value list:

DECLARE @i INT 
EXEC sp_executesql N‘select @i = 999‘, N‘@i int output‘, @i output 
SELECT @i 

-- You can provide several variables, of course. 

DECLARE @i INT, @j INT, @k VARCHAR(20) 
EXEC sp_executesql N‘select @i = 34, @j = 644, @k = ‘‘hello world‘‘‘, 

   N‘@i int output,@j int output,@k Varchar(20) output‘,  
   @i output, @j output, @k output 
SELECT @i, @j, @K  

And here is an example of the technique, where one of number of stored procedures which pass back a string output variable is called dynamically. The name of the stored procedure is passed in @SPName and the Dynamic SQL is built up in @SPCall the return value is passed in @rc and any error number is passed into @Error:

DECLARE @OutputParameter VARCHAR(100) , 
        @error INT , 
        @SPName VARCHAR(128) , 
        @SPCall NVARCHAR(128) , 
        @rc INT 
SELECT @SPCall = ‘exec ‘ + @SPName + ‘ @OutputParameter output‘ 
EXEC @rc = sp_executesql @SPCall, N‘@OutputParameter varchar(100) output‘,
                                                  @OutputParameter output 
SELECT @Error = @@error 

Q. Gosh Pop, executeSQL seems extraordinarily useful. What other magic does it perform, that isn‘t in BOL?

Pop: Well, let me see, did you know that one can use it to execute dynamic SQL that relies on the database context, in the context of another database / server? No?

Well, it works like this….

EXEC (‘exec svr.dbname.dbo.sp_executesql N‘‘grant select on mytable
                                                        to myuser‘‘‘)  

This will allow myuser to access mytable on the remote server.So. Let‘s imagine you want to write a stored procedure that grants access to a user, @username, on any table, @tablename, in any database, @databasename, based on any server, @servername. You might then use this trick to go about it like this:

DECLARE @sql VARCHAR(1000) 
SELECT @sql = ‘exec ‘ + @servername + ‘.‘ + @databasename 
                      + ‘.dbo.sp_executesql N‘‘grant select on ‘
                      + @tablename + ‘ to ‘ + @username + ‘‘‘‘ 
EXEC (@sql)  

Now run along you young scallywags…and make sure you have execute access to the remote sp_executesql before you try that out!!