Why wont my if statement work, in my stored procedure
- by MyHeadHurts
Alright so i am not even sure if this is possible
I have a q_00 and q_01 and q_02 which are all in my stored procedure.
then on the bottom i have 3 select statements
that select a certain catagory for example Sales,Net Sales and INS sales
What i want to be able to do is if the user types exec (name of my sp) (sales) (and a year which is the @yearparameter) it will run the sales select statement
If they type Exec (name of my SP) netsales (@Yeartoget) it will show the net sales is this possible or do i need multiple stored procedures
ALTER PROCEDURE [dbo].[casof]
@YearToGet int,
@mode VARCHAR(20)
as
;
with
q_00 as (
select
DIVISION
, SDESCR
, DYYYY
, sum(APRICE) as asofSales
, sum(PARTY) as asofPAX
, sum(NetAmount) as asofNetSales
, sum(InsAmount) as asofInsSales
, sum(CancelRevenue) as asofCXSales
, sum(OtherAmount) as asofOtherSales
, sum(CXVALUE) as asofCXValue
from dbo.B101BookingsDetails
where Booked <= CONVERT(int,DateAdd(year, @YearToGet - Year(getdate()), DateAdd(day, DateDiff(day, 1, getdate()), 0)))
and DYYYY = @YearToGet
group by DIVISION, SDESCR, DYYYY
),
q_01 as (
select
DIVISION
, SDESCR
, DYYYY
, sum(APRICE) as YESales
, sum(PARTY) as YEPAX
, sum(NetAmount) as YENetSales
, sum(InsAmount) as YEInsSales
, sum(CancelRevenue) as YECXSales
, sum(OtherAmount) as YEOtherSales
, sum(CXVALUE) as YECXValue
from dbo.B101BookingsDetails
where DYYYY=@YearToGet
group by DIVISION, SDESCR, DYYYY
),
q_02 as (
select
DIVISION
, SDESCR
, DYYYY
, sum(APRICE) as CurrentSales
, sum(PARTY) as CurrentPAX
, sum(NetAmount) as CurrentNetSales
, sum(InsAmount) as CurrentInsSales
, sum(CancelRevenue) as CurrentCXSales
, sum(OtherAmount) as CurrentOtherSales
, sum(CXVALUE) as CurrentCXValue
from dbo.B101BookingsDetails
where Booked <= CONVERT(int,DateAdd(year, (year( getdate() )) - Year(getdate()), DateAdd(day, DateDiff(day, 1, getdate()), 0)))
and DYYYY = (year( getdate() ))
group by DIVISION, SDESCR, DYYYY
)
IF @mode = 'sales'
select
a.DIVISION
, a.SDESCR
, a.DYYYY
, asofSales
, asofPAX
, YESales
, YEPAX
, CurrentSales
, CurrentPAX
, asofsales/ ISNULL(NULLIF(yesales,0),1) as percentsales
, asofpax/yepax as percentpax
,currentsales/ISNULL(NULLIF((asofsales/ISNULL(NULLIF(yesales,0),1)),0),1) as projectedsales
,currentpax/ISNULL(NULLIF((asofpax/ISNULL(NULLIF(yepax,0),1)),0),1) as projectedpax
from q_00 as a
join q_01 as b on (b.DIVISION = a.DIVISION and b.SDESCR = a.SDESCR and b.DYYYY = a.DYYYY)
join q_02 as c on (b.DIVISION = c.DIVISION and b.SDESCR = c.SDESCR)
order by a.DIVISION, a.SDESCR, a.DYYYY ;
else if @mode= 'netsales'
select
a.DIVISION
, a.SDESCR
, a.DYYYY
, asofPAX
, asofNetSales
, YEPAX
, YENetSales
, CurrentPAX
, CurrentNetSales
, asofnetsales/ ISNULL(NULLIF(yenetsales,0),1) as percentnetsales
, asofpax/yepax as percentpax
,currentnetsales/ISNULL(NULLIF((asofnetsales/ISNULL(NULLIF(yenetsales,0),1)),0),1) as projectednetsales
,currentpax/ISNULL(NULLIF((asofpax/ISNULL(NULLIF(yepax,0),1)),0),1) as projectedpax
from q_00 as a
join q_01 as b on (b.DIVISION = a.DIVISION and b.SDESCR = a.SDESCR and b.DYYYY = a.DYYYY)
join q_02 as c on (b.DIVISION = c.DIVISION and b.SDESCR = c.SDESCR)
order by a.DIVISION, a.SDESCR, a.DYYYY ;
ELSE IF @mode = 'inssales'
select
a.DIVISION
, a.SDESCR
, a.DYYYY
, asofPAX
, asofInsSales
, YEPAX
, YEInsSales
, CurrentPAX
, CurrentInsSales
, asofinssales/ ISNULL(NULLIF(yeinssales,0),1) as percentsales
, asofpax/yepax as percentpax
,currentinssales/ISNULL(NULLIF((asofinssales/ISNULL(NULLIF(yeinssales,0),1)),0),1) as projectedinssales
from q_00 as a
join q_01 as b on (b.DIVISION = a.DIVISION and b.SDESCR = a.SDESCR and b.DYYYY = a.DYYYY)
join q_02 as c on (b.DIVISION = c.DIVISION and b.SDESCR = c.SDESCR)
order by a.DIVISION, a.SDESCR, a.DYYYY ;
