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

70 views Asked by At

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?

1

There are 1 answers

0
Sean Lange On BEST ANSWER

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