How to set variable/output parameter in Dynamic SQL

To execute Dynamic SQL in
SQL Server you can use the
sp_executesql system sp or
EXEC() operator.







When you use the sp_executesql you can pass input and output
parameters. The EXEC() operator does not allow this.
By specifying the "OUTPUT" word in parameters definition
you can get the value back from the dynamic string execution.


Here are examples of setting a variable(s) from Dynamic SQL:
DECLARE @ID INT,@Str VARCHAR(10)
EXEC sp_executesql N'SELECT @ID=10,@Str=''Test''', N'@ID INT OUTPUT,@Str VARCHAR(10) OUTPUT',@ID OUTPUT,@Str OUTPUT
SELECT @ID,@Str

DECLARE @SQLString NVARCHAR(200),@ParmDefinition NVARCHAR(100)
DECLARE @IntVariable INT,@Lastlname VARCHAR(30)
SET @IntVariable=35
SET @SQLString = N'SELECT @LastlnameOUT = max(lname) FROM pubs.dbo.employee WHERE job_lvl = @level'
SET @ParmDefinition = N'@level tinyint,@LastlnameOUT varchar(30) OUTPUT'
EXECUTE sp_executesql @SQLString,@ParmDefinition,@level=@IntVariable,@LastlnameOUT=@Lastlname OUTPUT
SELECT @Lastlname
Here is an example of getting output parameter from a SP:

CREATE PROCEDURE dbo.usp_TestProc
(
@SomeValueToReturnFromSP VARCHAR(10) OUTPUT
)ASSELECT @SomeValueToReturnFromSP='TestString'GO

-----------------------------------------------------------------
DECLARE @OutputValueFromSP VARCHAR(10)
EXEC sp_executesql N'EXEC dbo.usp_TestProc @SomeValueToReturnFromSP=@OutputValueFromSP OUTPUT',N'@OutputValueFromSP VARCHAR(10) OUTPUT',@OutputValueFromSP OUTPUT
SELECT @OutputValueFromSP

GO

For more information:

http://support.microsoft.com/kb/262499

Tweet