cannot get the output parameter from an SP called from another SP

Question

I am trying to get output parameter value from a stored procedure that is called by another stored procedure but I didn't succeed. I am setting the output parameter respectively but it returns NULL.

ALTER PROCEDURE [dbo].[btn_usp_label_next_lp]
    @out_vchMsg                 NVARCHAR(30)    OUTPUT,
    @out_vchCode                NVARCHAR(30)    OUTPUT
AS
    DECLARE  @v_nSysErrorNum INTEGER

    EXEC usp_get_next_value_dummy 'BTN_DUMMY_LP', @out_vchCode OUTPUT, @v_nSysErrorNum OUTPUT, @out_vchMsg OUTPUT

Output parameter @out_vchCode is always null, but the stored procedure usp_get_next_value_dummy fills the @out_vchCode. I don't understand why it returns null above.

Can anybody give me an idea?

Answer 1

You need to make variables scoped to your procedure. They are defined as output but you are then trying to pass them along to another proc. I am not a huge fan of nested procs because the maintenance can get really ugly but here is how you could do this.

ALTER PROCEDURE [dbo].[btn_usp_label_next_lp]
    @out_vchMsg                 NVARCHAR(30)    OUTPUT,
    @out_vchCode                NVARCHAR(30)    OUTPUT
AS
    DECLARE  @v_nSysErrorNum INTEGER
        , @vchCode NVARCHAR(30)
        , @vchMsg NVARCHAR(30)

    EXEC usp_get_next_value_dummy 'BTN_DUMMY_LP', @vchCode OUTPUT, @v_nSysErrorNum OUTPUT, @vchMsg OUTPUT

    SELECT @out_vchCode = @vchCode
        , @out_vchMsg = @vchMsg