![\"277-Pop%20Rivett2.jpg\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/277-Pop%20Rivett2.jpg\")
Q<\/b>: Pop Rivett, how do I set variables from dynamically-executed SQL that I can then access?’<\/p>\nQ<\/b>: Pop Rivett, how do I set variables from dynamically-executed SQL that I can then access?’<\/p>\n
Pop<\/b>: 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<\/b> 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…<\/p>\n
Anyway, I digress. Here is my solution its minimal form. In the list of parameters that you pass to sp_ExecuteSQL<\/b>, 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:<\/p>\n 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<\/b> and the Dynamic SQL is built up in @SPCall<\/b> the return value is passed in @rc<\/b> and any error number is passed into @Error<\/b>:<\/p>\n Q<\/b>. Gosh Pop, executeSQL<\/b> seems extraordinarily useful. What other magic does it perform, that isn’t in BOL?<\/p>\n Pop<\/b>: 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? <\/p>\n Well, it works like this….<\/p>\n This will allow myuser<\/b> to access mytable<\/b> on the remote server.So. Let’s imagine you want to write a stored procedure that grants access to a user, @username<\/b>, on any table, @tablename<\/b>, in any database, @databasename<\/b>, based on any server, @servername.<\/b> You might then use this trick to go about it like this:<\/p>\n Now run along you young scallywags…and make sure you have execute access to the remote sp_executesql<\/b> before you try that out!!<\/p>\n","protected":false},"excerpt":{"rendered":" Pop Rivett dispenses pearls of SQL Server wisdom, this time tackling the setting of variables from dynamic SQL. Bah, humbug.…<\/p>\n","protected":false},"author":143519,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143525],"tags":[4419,4149,4150,4151,4418,4183],"coauthors":[8266],"class_list":["post-157","post","type-post","status-publish","format-standard","hentry","category-learn","tag-dynamic-sql","tag-learn-sql-server","tag-sql","tag-sql-server","tag-sql-server-faq","tag-t-sql"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/157","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/143519"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=157"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/157\/revisions"}],"predecessor-version":[{"id":92153,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/157\/revisions\/92153"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=157"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=157"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=157"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=157"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}\r\nDECLARE @i INT \r\nEXEC sp_executesql N'select @i = 999', N'@i int output', @i output \r\nSELECT @i\r\n\r\n-- You can provide several variables, of course.\r\n\r\nDECLARE @i INT, @j INT, @k VARCHAR(20) \r\nEXEC sp_executesql N'select @i = 34, @j = 644, @k = ''hello world''',\r\n\r\n N'@i int output,@j int output,@k Varchar(20) output', \r\n @i output, @j output, @k output \r\nSELECT @i, @j, @K <\/pre>\n
\r\nDECLARE @OutputParameter VARCHAR(100) , \r\n @error INT , \r\n @SPName VARCHAR(128) , \r\n @SPCall NVARCHAR(128) , \r\n @rc INT \r\nSELECT @SPCall = 'exec ' + @SPName + ' @OutputParameter output' \r\nEXEC @rc = sp_executesql @SPCall, N'@OutputParameter varchar(100) output',\r\n@OutputParameter output \r\nSELECT @Error = @@error<\/pre>\n
EXEC ('exec svr.dbname.dbo.sp_executesql N''grant select on mytable\r\nto myuser''') <\/pre>\n\r\nDECLARE @sql VARCHAR(1000) \r\nSELECT @sql = 'exec ' + @servername + '.' + @databasename \r\n+ '.dbo.sp_executesql N''grant select on '\r\n+ @tablename + ' to ' + @username + '''' \r\nEXEC (@sql) <\/pre>\n