sql-server - 我可以在 UDF 中使用 If 吗？

Question

我有一个作业，我应该使用标量 UDF 来实现一项业务规则，该规则规定所有报值(value)都会吸引以下佣金:

• 第一季度(一月至三月)10% 佣金

• 第二季度(四月至六月)15% 佣金

• 第二季度(7 月至 9 月)20% 佣金

• 第四季度(10 月至 12 月)25% 佣金

UDF 函数应接受报价，计算佣金并返回要支付给员工的佣金。

我有一个名为“Campaign Offer”的表。

Create Table Campaign_Offer
(
       Offer_id         integer identity(1,1) primary key,

       Offer_date           Date        ,

       Customer_id          int ,

       Offer_value          money       ,

       Product_name     varchar(20) ,

       Acceptance_status    varchar(3)  ,

       Employee_ID          char(10)    ,

    Constraint CustomerID foreign key (Customer_id) references marketing_list(Id),
    Constraint EmployeeID foreign key (Employee_ID) references Employee(Employee_id),
    Constraint AcceptanceStatus Check (Acceptance_status in ('yes', 'no'))
)

我想在这里做

Create Function OfferCommission (@OfferValue money,@OfferDate date)
Returns money
As
Begin
if @OfferDate between '01/01/2015' and '03/31/2015'
Begin
Return @OfferValue*0.1
End
else
if @OfferDate between '04/01/2015' and '06/31/2015'
Begin
Return @OfferValue*0.15
End
else
if @OfferDate between '07/01/2015' and '09/31/2015'
Begin
Return @OfferValue*0.2
End
else
if @OfferDate between '09/01/2015' and '12/31/2015'
Begin
Return @OfferValue*0.25
End
End

Answer 1

您可以使用CASE

Create Function OfferCommission (@OfferValue money,@OfferDate date)
Returns money
As
Begin
 DECLARE @result MONEY;

 SELECT @result = 
   CASE 
    WHEN @OfferDate between '01/01/2015' and '03/31/2015' THEN @OfferValue * 0.1
    WHEN @OfferDate between '04/01/2015' and '06/31/2015' THEN @OfferValue * 0.15
    WHEN @OfferDate between '07/01/2015' and '09/31/2015' THEN @OfferValue * 0.2
    WHEN @OfferDate between '09/01/2015' and '12/31/2015' THEN @OfferValue * 0.25
    ELSE NULL
    END;

  RETURN @result;
END

您可以使用CHOOSE为SQL SERVER 2012+提供更短的解决方案。

Create Function OfferCommission (@OfferValue money,@OfferDate date)
Returns money
As
Begin
 RETURN @OfferValue * CHOOSE(DATEPART(QUARTER,@OfferDate), 0.1, 0.15, 0.2, 0.25) 
END