Microsoft KB Archive/262499

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

Article ID: 262499

Article Last Modified on 2/22/2007

-

APPLIES TO


 * 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

-



This article was previously published under Q262499



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 INT DECLARE @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 = 35 EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable, @LastlnameOUT=@Lastlname OUTPUT SELECT @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' + @parm GO DECLARE @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 OUTPUT

SELECT @parmRET1 AS &quot;parameter 1&quot;, @parmRET2 AS &quot;parameter 2&quot; go drop procedure Myproc For additional information, see &quot;sp_executesql (T-SQL)&quot; and &quot;Using sp_executesql&quot; in SQL Server Books Online. For additional information, see &quot;Using sp_executesql&quot; in SQL Server Books Online, and &quot;sp_executesql (T-SQL)&quot; in SQL Server 7.0 Books Online or &quot;sp_executesql&quot; in SQL Server 2000 Books Online.

Keywords: kbinfo KB262499

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.