One of my coworkers asked me about output parameters from dynamic SQL today. I pointed him at sp_executesql, which allows for passing parameters into or out of dynamic SQL. The syntax can be confusing at first blush (especially for a new DBA or developer), but it’s really pretty straightforward, easy, and useful.
It’s scary because there are a lot of variables. Often time, people use the same name for both inside the dynamic SQL & outside in your “regular” SQL. Sometimes you surround your parameters in quotes, and other times you don’t.
I whipped up a quick example for him, which he declared (I’m paraphrasing) “the easiest to understand example that I’ve seen for sp_executesql”:
DECLARE @i int, @sql nvarchar(1000), @params nvarchar(1000); SET @params = N'@i_out int OUT'; SET @sql = N'SELECT @i_out = 1'; EXEC sp_executesql @sql, @params, @i_out = @i OUT; SELECT @i;
The trick is to think of this as a dynamic stored procedure where the definition & procedure call all happen at the same time.
@sql is the body of the stored procedure definition (ie, the part after CREATE PROCEDURE…AS…”.
@params is the parameter definition list.
Then, when you call sp_executesql with parameters, remember to think of the parts in the same terms:
EXEC sp_executesql [stored proc definition], [parameter definition list], [parameter assignments];
If you wanted to use multiple parameters, a slightly more complicated example would look something like this:
DECLARE @i int, @x int, @sql nvarchar(1000), @params nvarchar(1000); SET @params = N'@i_out int OUT, @x int'; SET @sql = N'SELECT @i_out = 1 WHERE @x = 2'; EXEC sp_executesql @sql, @params, @i_out = @i OUT, @x = 3; SELECT @i; EXEC sp_executesql @sql, @params, @i_out = @i OUT, @x = 2; SELECT @i;