How to specify output parameters when you use the sp_executesql stored procedure in SQL Server

The sp_executesql system stored procedure is used to execute a T-SQL statement which can be reused multiple times, or to execute a dynamically built T-SQL statement. It takes parameters as inputs in order to process the T-SQL statements or batches. It also allows output parameters to be specified so that any output generated from the T-SQL statements can be stored (although this is not documented in SQL Server Books Online).

Two scenarios in which the output parameters will be useful with sp_executesql are:
  • If sp_executesql generates output that will be useful, storing this output to an output parameter allows the calling batch to use the parameter for later queries.
  • If sp_executesql is executing a stored procedure that is defined using output parameters, the output parameters for sp_executesql can be used to hold the outputs generated from the stored procedure.
More information
The following two examples demonstrate the use of output parameters with sp_executesql.

Example 1
DECLARE @SQLString NVARCHAR(500)DECLARE @ParmDefinition NVARCHAR(500)DECLARE @IntVariable INTDECLARE @Lastlname varchar(30)SET @SQLString = N'SELECT @LastlnameOUT = max(lname)                   FROM pubs.dbo.employee WHERE job_lvl = @level'SET @ParmDefinition = N'@level tinyint,                        @LastlnameOUT varchar(30) OUTPUT'SET @IntVariable = 35EXECUTE sp_executesql@SQLString,@ParmDefinition,@level = @IntVariable,@LastlnameOUT=@Lastlname OUTPUTSELECT @Lastlname				
Example 2
CREATE PROCEDURE Myproc    @parm varchar(10),    @parm1OUT varchar(30) OUTPUT,    @parm2OUT varchar(30) OUTPUT    AS      SELECT @parm1OUT='parm 1' + @parm     SELECT @parm2OUT='parm 2' + @parmGODECLARE @SQLString NVARCHAR(500)DECLARE @ParmDefinition NVARCHAR(500)DECLARE @parmIN VARCHAR(10)DECLARE @parmRET1 VARCHAR(30)DECLARE @parmRET2 VARCHAR(30)SET @parmIN=' returned'SET @SQLString=N'EXEC Myproc @parm,                             @parm1OUT OUTPUT, @parm2OUT OUTPUT'SET @ParmDefinition=N'@parm varchar(10),                      @parm1OUT varchar(30) OUTPUT,                      @parm2OUT varchar(30) OUTPUT'EXECUTE sp_executesql    @SQLString,    @ParmDefinition,    @parm=@parmIN,    @parm1OUT=@parmRET1 OUTPUT,@parm2OUT=@parmRET2 OUTPUTSELECT @parmRET1 AS "parameter 1", @parmRET2 AS "parameter 2"godrop procedure Myproc				
For additional information, see "sp_executesql (T-SQL)" and "Using sp_executesql" in SQL Server Books Online.For additional information, see "Using sp_executesql" in SQL Server Books Online, and "sp_executesql (T-SQL)" in SQL Server 7.0 Books Online or "sp_executesql" in SQL Server 2000 Books Online.

Article ID: 262499 - Last Review: 06/22/2014 11:13:00 - Revision: 5.0

  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • kbsqlsetup kbinfo KB262499