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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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:
|
1 2 3 4 5 6 7 8 9 |
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….
|
1 2 |
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:
|
1 2 3 4 5 |
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!!
Load comments