sql - 为什么我的 if 语句在我的存储过程中不起作用

Question

好吧，所以我什至不确定这是否可能
我有一个 q_00 和 q_01 和 q_02，它们都在我的存储过程中。
然后在底部我有 3 个选择语句
选择某个类别，例如销售额、净销售额和 INS 销售额

我希望能够做的是，如果用户输入 exec(我的 sp 的名称)(销售)(以及作为 @yearparameter 的年份)，它将运行销售选择语句

如果他们输入 Exec(我的 SP 的名称)netsales(@Yeartoget)，它会显示净销售额是可能的还是我需要多个存储过程

   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 ;

Answer 1

只需添加另一个名为 say @mode 的参数并使用 if @mode='sales'执行条件逻辑。

在您更新之后，我很想创建两个辅助参数化内联 TVF。

CREATE FUNCTION dbo.AggregateBookingDetails  
(   
    @Booked datetime, 
    @YearToGet int
)
RETURNS TABLE 
AS
RETURN 
(
select
      DIVISION
    , SDESCR
    , DYYYY
    , sum(APRICE)        as Sales 
    , sum(PARTY)         as PAX        
    , sum(NetAmount)     as NetSales        
    , sum(InsAmount)     as InsSales        
    , sum(CancelRevenue) as CXSales        
    , sum(OtherAmount)   as OtherSales        
    , sum(CXVALUE)       as CXValue  
from dbo.B101BookingsDetails 
where @Booked IS NULL OR Booked <= @Booked
  and DYYYY = @YearToGet
group by DIVISION, SDESCR, DYYYY 
)

走

CREATE FUNCTION fn_casof
(   
    @YearToGet int
)
RETURNS TABLE 
AS
RETURN 
(
select       
      a.DIVISION
    , a.SDESCR
    , a.DYYYY
    , a.Sales as a_Sales
    , b.Sales as b_Sales
    , c.Sales as c_Sales
    , .... /*etc. etc*/
from dbo.AggregateBookingDetails(CONVERT(int,DateAdd(year, @YearToGet - Year(getdate()), DateAdd(day, DateDiff(day, 1, getdate()), 0))), @YearToGet) as a
join dbo.AggregateBookingDetails(NULL, @YearToGet) as b on (b.DIVISION = a.DIVISION and b.SDESCR = a.SDESCR and b.DYYYY = a.DYYYY) 
join dbo.AggregateBookingDetails(CONVERT(int,DateAdd(year, (year( getdate() )) - Year(getdate()), DateAdd(day, DateDiff(day, 1, getdate()), 0))), year( getdate() )) as c on (b.DIVISION = c.DIVISION and b.SDESCR = c.SDESCR)
)

然后，您的存储过程条件逻辑只需从这些 TVF 中的第二个中选择所需的列。

ALTER PROCEDURE [dbo].[casof]
@YearToGet int,
@mode VARCHAR(20)
as
IF (@mode='sales')
SELECT collist1 FROM dbo.fn_casof(@yeartoget)
ELSE
    IF (@mode='netsales')
    SELECT collist2 FROM dbo.fn_casof(@yeartoget)
    ELSE
    SELECT collist3 FROM dbo.fn_casof(@yeartoget)