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;

2 Comments

  1. Hi,

    I am unable to get the resultset from the stored procedure

    	DECLARE @SQL nvarchar(max)
            SET @SQL = 'SELECT DISTINCT  ' + @Columns +  '...WHERE d.searchid = @pSearchID';
            EXEC  sp_executesql @SQL, N'@pSearchID BIGINT OUT',@pSearchID = @SearchID  OUT
    

    I am unable to get result when executing the Stored producure containing the above code. Please Help

    • You’ve declared @SearchID as an output parameter, when it’s really just a regular old input parameter.

      Consider this example, using sys.columns as the table. Other than table & column names, the only change I made was to remove the OUT keyword in both spots:

      DECLARE @Columns nvarchar(max) = N'name, column_id';
      DECLARE @SearchID BIGINT = 5;
      DECLARE @SQL nvarchar(max);
      SET @SQL = 'SELECT DISTINCT ' + @Columns + ' FROM sys.columns d WHERE d.object_id = @pSearchID';
      
      SELECT @SQL
      
      EXEC sp_executesql @SQL, N'@pSearchID BIGINT',@pSearchID = @SearchID;
      

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.