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

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

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.

Solution

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.