retrive values from another stored procedure

Question

Good morning !

Please help me with this,  how could I retrive a value from this store procedure  "P5" to another sp "P6",

I just need the to retrive the value of @totalroom     

 

USE [tonydb]
GO
/****** Object:  StoredProcedure [tonyuser].[p5]    Script Date: 10/02/2011 11:58:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [tonyuser].[p5]
 @Checkin datetime ,
 @Checkout datetime,
 @IDHotel int,
 @IDRTYPE int,
 @IDPlan int,
 @Quantdbl int,
 @Quantsgl int,
 @Quanttpl int,
 @Quantqdpl int,
 @Quantchd int,
 @nights int OUTPUT,
 @totalroom numeric OUTPUT,
 @average numeric OUTPUT,
 @Sum numeric OUTPUT,
 @SumSGL numeric OUTPUT,
 @SumTPL numeric OUTPUT,
 @SumCUAD numeric OUTPUT,
 @SumCHD numeric OUTPUT

AS
WITH
 t1 AS (SELECT 0 n UNION ALL SELECT 0 n UNION ALL SELECT 0 UNION ALL SELECT 0)
 ,Calendario AS (SELECT DATEADD(day, (ROW_NUMBER() OVER (ORDER BY a.n)), @CheckIn) AS Fetcha
  FROM t1 a, t1 b, t1 c, t1 d, t1 e, t1 f) 

SELECT @nights= convert(int,@Checkout - @CheckIn), @Sum = SUM(dbl*@quantdbl),@SumSGL = SUM(SGL*@quantsgl), @SumTPL = SUM(isnull(Triple,0)*@quanttpl),@SumCUAD = SUM(isnull(Cuad,0)*@quantqdpl),@SumChd = SUM(isnull(Chd,0)*@quantchd), @totalroom = (@Sum+@SumSGL+@SumTPL+@SumCUAD+@SumChd), @average = (@totalroom/@nights) 
FROM hotelsnew
JOIN Calendario ON 

Calendario.Fetcha BETWEEN datefrom AND dateto
     
WHERE

 Hoteles = @IDHotel AND tiporoom = @IDType AND Id_plan = @IDPlan

 RETURN

Answer 1

Try:

declare @tr numeric
exec tonuyuser.p5
...
,  @tr = @totalroom output
...

select @tr

 

---

Tom
SQL Server MVP
Toronto, ON Canada

Answer 2

Please call the stored procedure like below

 

declare @OutputNights int
declare @Outputtotalroom numeric 
declare @Outputaverage numeric 
declare @OutputSum numeric 
declare @OutputSumSGL numeric 
declare @OutputSumTPL numeric 
declare @OutputSumCUAD numeric 
declare @OutputSumCHD numeric 

exec [tonyuser].[p5]
 @Checkin ='date' ,
 @Checkout ='date',
 @IDHotel = hotelID,
 @IDRTYPE =idrtype,
 @IDPlan =idplan,
 @Quantdbl=Quantdbl,
 @Quantsgl=Quantsgl,
 @Quanttpl=Quanttpl,
 @Quantqdpl=Quantqdpl,
 @Quantchd=Quantchd,
 @nights =@OutputNights OUTPUT, 
 @totalroom=@Outputtotalroom OUTPUT,
 @average=@Outputaverage OUTPUT, 
 @Sum=@OutputSum OUTPUT,
 @SumSGL=@OutputSumSGL OUTPUT,
 @SumTPL=@OutputSumTPL OUTPUT,
 @SumCUAD=@OutputSumCUAD OUTPUT,
 @SumCHD=@OutputSumCHD OUTPUT

You can then insert the above declared variables into the stored procedure P6 as input parameters.