CODEWITHSUNDEEP
sqlsql-servert-sqlexec
wondra
wondra

Reputation: 3583

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.

Upvotes: 4

Views: 3095

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175924

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

Upvotes: 6

Related Questions