sp_executesql with output parameters

Hoover Dam intake towers beside Lake Mead, with reservoir water in the foreground.
These towers accept the input parameters for the Hoover Dam.
(Photo: Andy Mallon, Hoover Dam, Jan 2023)

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 sys.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 and procedure call all happen at the same time. @sql is the body of the stored procedure definition (i.e., the part after CREATE PROCEDURE...AS...). @params is the parameter definition list.

Downstream view of the Hoover Dam power plant and Colorado River beneath the arch bridge.
The output parameters of the Hoover Dam are tightly controlled. Photo credit: Andy Mallon, Hoover Dam, Jan 2023.

Then, when you call sp_executesql with parameters, remember to think of the parts in the same terms:

EXEC sys.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 sys.sp_executesql @sql, @params, @i_out = @i OUT, @x = 3;
SELECT @i;

EXEC sys.sp_executesql @sql, @params, @i_out = @i OUT, @x = 2;
SELECT @i;

Categories: Databases

Tags: Sp_executesql, T-SQL

Comments migrated from WordPress

  1. Susan

    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

  2. Andy

    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;

Join the conversation

Sign in with GitHub to comment.

GitHub subscribes you to discussions you participate in or are mentioned in.
Use Watch → Custom on the comments repository to follow Discussions only;
control email delivery in your GitHub notification settings.