How to Pass Output Parameter Store Procedure Value to input to another SP

• Sangeeth Raj

  Hall of Fame

  Points: 3541

  More actions

  November 9, 2016 at 9:10 am

  #331613

  Hi All,

  I have two store procedure,

  i) Proc 1 (With Output Parameter) : No input parameter, one output Parameter

  ii) Proc 2 : One Input Parameter, No Output Parameters. ( I need to pass output parameter from Proc 1 to proc2 as Input Parameter)

  declare @Paramtopass varchar(300)

  declare @Value varchar(300)

  exec @Paramtopass = Dbo.Proc1 @No OUTPUT --- Need to pass this value into another Proc2

  Select @ParamtoPass --- No Output , Empty 🙁

  Exec dbo.proc2 @ParamtoPass

  I have tried above script, but it is not working, Could someone please assist with me.

  Many thanks

• Luis Cazares

  SSC Guru

  Points: 183686

  More actions

  November 9, 2016 at 9:15 am

  #1911428

  Here's an example:

  CREATE PROCEDURE ProcedureWithOutputParam(

  @OutputParam int OUTPUT

  )

  AS

  SET @OutputParam = 5;

  GO

  CREATE PROCEDURE ProcedureWithInputParam(

  @Param int

  )

  AS

  SELECT @Param AS Parameter;

  GO

  DECLARE @Parameter int = NULL;

  EXEC ProcedureWithOutputParam @OutputParam = @Parameter OUTPUT;

  EXEC ProcedureWithInputParam @Param = @Parameter;

  GO

  DROP PROCEDURE ProcedureWithInputParam;

  DROP PROCEDURE ProcedureWithOutputParam;

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Eirikur Eiriksson

  SSC Guru

  Points: 182631

  More actions

  November 9, 2016 at 9:20 am

  #1911431

  Quick question, have you tried to execute the statement within the first procedure and see if it is returning any value?

  😎

• John Mitchell-245523

  SSC Guru

  Points: 148786

  More actions

  November 9, 2016 at 9:28 am

  #1911432

  The reason your script fails is that the [font="Courier New"]EXEC @Variable = ProcName[/font] syntax writes the return value, not the output parameter value, into the @Variable variable. Return values are optional, so you don't always see them in stored procedures. They can be anything, but are usually used to indicate the status (eg success, failure, row count etc) of a process within the procedure. Your code should work if you replace the last two occurrences of [font="Courier New"]@ParamtoPass[/font] with [font="Courier New"]@No[/font].

  John