Pop Rivett’s SQL Server FAQ

Pop Rivett dispenses pearls of SQL Server wisdom, this time tackling the setting of variables from dynamic SQL. Bah, humbug.

Setting a Variable from Dynamic SQL

277-Pop%20Rivett2.jpgQ: 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:

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:

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….

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:

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