SQL Server string exec a procedure with output parameter

Question

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.

Dan Guzman · Accepted Answer · 2016-05-01 15:24:00Z

6

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

edited May 1, 2016 at 15:24

Dan Guzman

46.9k3 gold badges52 silver badges77 bronze badges

answered May 1, 2016 at 15:01

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sqluser Over a year ago

Is it possible to return in dynamic SQL something like @a = exec (@sql). @a is coming from return value of proc not output param.

Collectives™ on Stack Overflow

SQL Server string exec a procedure with output parameter

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related