EXEC sp_executesql - capture RETURN value and OUTPUT value from stored procedure call

Question

Given a simple stored procedure, that populates a OUTPUT parameter and also RETURNs a value, such as:

CREATE PROCEDURE sp_test 
(
    @param_out INT OUTPUT
)
AS BEGIN
    SELECT @param_out = 9
    RETURN 2
END

How can I call this procedure using sp_executesql and capture both of these values?

I've tried:

DECLARE @ret INT, @param_out INT
EXEC SP_EXECUTESQL N'EXEC @ret = sp_test',
    N'@ret INT OUTPUT, @param_out INT OUTPUT',
    @ret OUTPUT,
    @param_out OUTPUT

SELECT @ret, @param_out

However this complains that @param_out was not supplied:

Procedure or function 'sp_test' expects parameter '@param_out', which was not supplied.

I'm actually dynamically building up the stored procedure name as a string, this was just an example to demonstrate the issues I was having with the OUTPUT and RETURN values. — devklick, Mar 15 '19 at 11:36

score 5 · Accepted Answer · answered Mar 15 '19 at 11:23

5

You would need to pass both values as OUTPUT parameters to sp_executesql as well:

DECLARE @ret int,
        @param_out int;
EXEC sp_executesql N'EXEC @ret = sp_test @param_out OUT;',
                   N'@ret INT OUTPUT, @param_out INT OUTPUT',
                   @ret OUTPUT,
                   @param_out OUTPUT;

SELECT @ret,
       @param_out;

I must ask though, why do you want to do this? There's nothing dynamic about this SQL, so why are you using sp_executesql? I also advise against using the return value of an SP; you should really be using another OUTPUT parameter.

answered Mar 15 '19 at 11:23

Thom A

88,727
11
45
75

1

Adding that the stored procedure return code should be used to indicate success or warning/error, not to return data. – Dan Guzman Mar 15 '19 at 11:26
Perfect, I see my mistake quite clearly now that you've pointed it out. Your right, there's nothing dynamic about this example, but I actually dynamically build up the procedure to call in my 'real' SQL. Thanks for your help! – devklick Mar 15 '19 at 11:32

score 1 · Answer 2 · answered Mar 15 '19 at 12:10

You don't need sp_executesql here just because the stored procedure name is dynamic.

The EXEC grammar accepts @module_name_var.

You can do

DECLARE @ret       INT,
        @param_out INT
DECLARE @procname SYSNAME = 'sp_test'

EXEC @ret = @procname
  @param_out OUTPUT

and the procedure with the name in @procname will be executed

EXEC sp_executesql - capture RETURN value and OUTPUT value from stored procedure call

2 Answers2