Pop Rivett‘s SQL Server FAQ

18 September 2006

by Pop Rivett

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!!

This article has been viewed 96 times.

Author profile: Pop Rivett

Pop spent his formative years working in assembler on IBM Series/1 but retrained in VB when that went out of fashion. He soon realised how little relational database expertise existed in most companies and so started to spend most of his time working on that. He now sticks to architecture, release control / IT processes, SQL Server, DTS, SSIS, and access methods in VB/ASP/.NET/Crystal Reports/reporting services. He has been involved with SQL Server since the old days of v4.2 to v2005. He tries to stay away from anything presentation oriented (see www.mindsdoor.net). Theoretically he is semi-retired but seems to keep being offered potentially interesting work.

Search for other articles by Pop Rivett