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

Extended support for SQL Server 2005 ended on April 12, 2016

If you are still running SQL Server 2005, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

Summary
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.
Properties

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
Feedback