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?
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.