join 2 stored procedures in another stored procedure
I have two stored procedures for instanse test1 and test2 and both use the same parameter X.
X is part of an if statement in both these stored procedures, basically it tells the the procedure which select statement to pick. (this shouldn't matter as it's a parameter but i'm not sure)
I want test1 and test2 to be joined by column name, as they will both have the column name, and i want them linked side by side not one underneath the other, is this possible and how
Basically, i want to join these 2 stored procedures in another stored procedure that my asp.net program will call.
this is stored procedure (sp_current)
@mode varchar(20)
AS
SET NOCOUNT ON
Declare @Booked Int
Set @Booked = CONVERT(int,DateAdd(year, (year( getdate() )) - Year(getdate() + 1),
DateAdd(day, DateDiff(day, 1, getdate()), 1) ) )
If @mode = 'Sales'
Select
Division,
SDESCR,
DYYYY,
Sum(Case When Booked <= @Booked Then NetAmount End) currentNetSales,
Sum(Case When Booked <= @Booked Then PARTY End) AS currentPAX
From dbo.B101BookingsDetails
Where DYYYY = (year( getdate() ))
Group By SDESCR, DYYYY, Division
Order By Division, SDESCR, DYYYY
else if @mode = 'netsales'
Select Division,
SDESCR,
DYYYY,
Sum(Case When Booked <= @Booked Then NetAmount End) currentNetSales,
Sum(Case When Booked <= @Booked Then PARTY End) AS currentPAX
From dbo.B101BookingsDetails
Where DYYYY = (year( getdate() ))
Group By SDESCR, DYYYY, Division
Order By Division, SDESCR, DYYYY
Else if @mode = 'Inssales'
Select Division,
SDESCR,
DYYYY,
Sum(Case When Booked <= @Booked Then InsAmount End) currentInsSales,
Sum(Case When Booked <= @Booked Then PARTY End) AS currentPAX
From dbo.B101BookingsDetails
Where DYYYY = (year( getdate() ))
Group By SDESCR, DYYYY, Division
Order By Division, SDESCR, DYYYY
Else if @mode = 'CXsales'
Select Division,
SDESCR,
DYYYY,
Sum(Case When Booked <= @Booked Then CancelRevenue End) currentCXSales,
Sum(Case When Booked <= @Booked Then PARTY End) AS currentPAX
开发者_JAVA百科From dbo.B101BookingsDetails
Where DYYYY = (year( getdate() ))
Group By SDESCR, DYYYY, Division
Order By Division, SDESCR, DYYYY
Else if @mode = 'othersales'
Select Division,
SDESCR,
DYYYY,
Sum(Case When Booked <= @Booked Then OtherAmount End) currentOtherSales,
Sum(Case When Booked <= @Booked Then PARTY End) AS currentPAX
From dbo.B101BookingsDetails
Where DYYYY = (year( getdate() ))
Group By SDESCR, DYYYY, Division
Order By Division, SDESCR, DYYYY
Else if @mode = 'cxvalue'
Select Division,
SDESCR,
DYYYY,
Sum(Case When Booked <= @Booked Then CXVALUE End) currentCXValue,
Sum(Case When Booked <= @Booked Then PARTY End) AS currentPAX
From dbo.B101BookingsDetails
Where DYYYY = (year( getdate() ))
Group By SDESCR, DYYYY, Division
Order By Division, SDESCR, DYYYY
and This is sp_compare
@mode varchar(20),
@YearToGet int
AS
SET NOCOUNT ON
Declare @Booked Int
Set @Booked = CONVERT(int,DateAdd(year, @YearToGet - Year(getdate() + 1),
DateAdd(day, DateDiff(day, 1, getdate()), 1) ) )
If @mode = 'Sales'
Select
Division,
SDESCR,
DYYYY,
Sum(Case When Booked <= @Booked Then NetAmount End) ASofNetSales,
SUM(NetAmount) AS YENetSales,
Sum(Case When Booked <= @Booked Then PARTY End) AS ASofPAX,
SUM(PARTY) AS YEPAX
From dbo.B101BookingsDetails
Where DYYYY = @YearToGet
Group By SDESCR, DYYYY, Division
Order By Division, SDESCR, DYYYY
else if @mode = 'netsales'
Select Division,
SDESCR,
DYYYY,
Sum(Case When Booked <= @Booked Then NetAmount End) ASofNetSales,
SUM(NetAmount) AS YENetSales,
Sum(Case When Booked <= @Booked Then PARTY End) AS ASofPAX,
SUM(PARTY) AS YEPAX
From dbo.B101BookingsDetails
Where DYYYY = @YearToGet
Group By SDESCR, DYYYY, Division
Order By Division, SDESCR, DYYYY
Else if @mode = 'Inssales'
Select Division,
SDESCR,
DYYYY,
Sum(Case When Booked <= @Booked Then InsAmount End) ASofInsSales,
SUM(InsAmount) AS YEInsSales,
Sum(Case When Booked <= @Booked Then PARTY End) AS ASofPAX,
SUM(PARTY) AS YEPAX
From dbo.B101BookingsDetails
Where DYYYY = @YearToGet
Group By SDESCR, DYYYY, Division
Order By Division, SDESCR, DYYYY
Else if @mode = 'CXsales'
Select Division,
SDESCR,
DYYYY,
Sum(Case When Booked <= @Booked Then CancelRevenue End) ASofCXSales,
SUM(CancelRevenue) AS YECXSales,
Sum(Case When Booked <= @Booked Then PARTY End) AS ASofPAX,
SUM(PARTY) AS YEPAX
From dbo.B101BookingsDetails
Where DYYYY = @YearToGet
Group By SDESCR, DYYYY, Division
Order By Division, SDESCR, DYYYY
Else if @mode = 'othersales'
Select Division,
SDESCR,
DYYYY,
Sum(Case When Booked <= @Booked Then OtherAmount End) ASofOtherSales,
SUM(OtherAmount) AS YEOtherSales,
Sum(Case When Booked <= @Booked Then PARTY End) AS ASofPAX,
SUM(PARTY) AS YEPAX
From dbo.B101BookingsDetails
Where DYYYY = @YearToGet
Group By SDESCR, DYYYY, Division
Order By Division, SDESCR, DYYYY
Else if @mode = 'cxvalue'
Select Division,
SDESCR,
DYYYY,
Sum(Case When Booked <= @Booked Then CXVALUE End) ASofCXValue,
SUM(CXVALUE) AS YECXValue,
Sum(Case When Booked <= @Booked Then PARTY End) AS ASofPAX,
SUM(PARTY) AS YEPAX
From dbo.B101BookingsDetails
Where DYYYY = @YearToGet
Group By SDESCR, DYYYY, Division
Order By Division, SDESCR, DYYYY
I either want to make it one procedure or somehow join them side by side in a seperate procedure
Your question is a bit vage. I guess that both procedures returna result set and you want to return a join of those result sets.
You can define temporary tables or table variables and use
INSERT INTO table_1 exec sp_1 ...
INSERT INTO table_2 exec sp_2 ...
Than you can select from a join from those 2 temporary objects.
EDIT: Not knowing the data-types let me try something like
create table #tmp1 (
Division varchar(50),
SDESCR varchar850),
DYYYY varchar(5),
currentNetSales money,
currentPax money
)
create table #tmp2 (
Division varchar(50),
SDESCR varchar(50),
DYYYY varchar(5),
ASofNetSales money,
YENetSales money,
ASofPAX money,
YEPAX money
)
INSERT INTO #tmp1 exec sp_current ('Sales')
INSERT INTO #tmp2 exec sp_compare ('Sales', 2009)
select t1.Division,
isnull(t1.SDESCR, t2.SDESCR) SDESCR,
isnull(t1.DYYYY, t2.DYYYY) DYYYY,
t1.currentNetSales,
t1.currentPax,
t2.ASofNetSales money,
t2.YENetSales money,
t2.ASofPAX money,
t2.YEPAX money
from #tmp1 t1
full outer join #tmp2 t2 on t1.Division = t2.Division and t1.SDESCR = t2.SDESCR
Order By Division, SDESCR
精彩评论