wondra wondra - 1 year ago 115
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

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

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


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

seems to work: the result is either
Must declare the scalar variable "@testString"

Answer Source

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;


Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download