sp_executesql with output parameters

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;