Link to home
Start Free TrialLog in
Avatar of HemlockPrinters
HemlockPrinters

asked on

how to pass one stored procedure's result as another stored procedure's parameter

First Stored Procedure SP1 has one parameter @job
Second Stored Procedure SP2 has one parameter @estno

executer SP1 @job will return the value of SP2 parameter, @estno = execute so1, @job

How can I create a new Stored procedure SP3, SP3 has one parameter @job, it will pass (executer SP1 @job) to @estno in SP2, and return the result of SP2

thanks
Avatar of Aneesh
Aneesh
Flag of Canada image

>executer SP1 @job will return the value of SP2 parameter, @estno = execute so1, @job
I am not sure how you are returning the value, if you are usind a return () option, execute the sp like this
declare @ret int
Exec @ret=  execute SP1 @job
exec so1 @ret

other option will be to return the result as an output parameter
http://msdn.microsoft.com/en-ca/library/ms187004(v=sql.105).aspx
Avatar of HemlockPrinters
HemlockPrinters

ASKER

Thanks, I still ddin't get the result.
SP1:


Alter PROCEDURE LjobToEstno
            @Ljob int

AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
      SELECT estno from bbjthead where ljob=@ljob

END


-----------------------------------------------------------------------------------------------------------------------------------

SP2:

ALTER PROCEDURE [dbo].[cq_est_estdetail_header_sp4]
      @tcEstNo      int = 0

AS

SELECT
      aajthead.estno,
      esttype,
      rolljob,
      rolldate,
......

---------------------------------------------------------------------------------------------------------------

now I created SP3:

create PROCEDURE [dbo].[cq_est_estdetail_header_sp5]
    @ljob int
    ,@tcEstNo int
AS
Exec @tcEstNo = ljobtoestno @ljob

SELECT
      aajthead.estno,
      esttype,
      rolljob,
      rolldate,
......

---------------------------------------------------------------------------------------------------------------
When I execute sp3, it still wants me to provide the value of @tcEstNo
ASKER CERTIFIED SOLUTION
Avatar of Aneesh
Aneesh
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks, it works great!
EXEC @tcEstNo = LjobToEstno @Ljob   ----This returns right value
select @tcEstNo
  EXEC [cq_est_estdetail_header_sp4] @tcEstNo    ---doesn't seem work,  0 value passed to @tcEstNo  -- if ir is returning 0, put a print statement inside the sp, to see whether you are assigning a value 0 to that variable