wondra wondra - 7 months ago 20
SQL Question

SQL Server string exec a procedure with output parameter

How do I use output parameters with Character String Exec?
Consider following query with "standard" syntax:

DECLARE @testString nvarchar(50);
EXEC testProcedure @param1 = @testString OUTPUT
SELECT 'result ' = @testString
go


How to re-create exactly same result with character string exec, neither

EXEC ( 'testProcedure @param1 = @testString OUTPUT' )


nor

EXEC ( 'testProcedure @param1 = ' + @testString + ' OUTPUT' )


seems to work: the result is either
NULL
or
Must declare the scalar variable "@testString"
error.

Answer

You need to pass the value as a parameter. Dynamic SQL is executed in new context that is why variable is not visible there.

You should use sp_executesql:

DECLARE @testString nvarchar(50);

EXEC dbo.sp_executesql N'EXEC testProcedure @testString OUTPUT',
                       N'@testString nvarchar(50) OUTPUT',
                       @testString OUTPUT;

SELECT 'result ' = @testString;

LiveDemo