CODEWITHSUNDEEP
sql-server
user3079559
user3079559

Reputation: 417

SQL Server Scalar UDF

I have a udf with multiple if statement. In the current form, it is returning incorrect results.

Here are the requirements:

This is my UDF:

ALTER FUNCTION[dbo].[getPaye] (@grosspay MONEY)
RETURNS MONEY
AS
BEGIN
    -------------different income  bands
    DECLARE @lowerlimit MONEY = 215000
    DECLARE @midlimit MONEY = 335000
    DECLARE @upperlimit MONEY = 410000
    DECLARE @newupperlimit MONEY = 10000000

   --------------different percentages
   DECLARE @lowerpercent AS DECIMAL(3, 2) = 0.1
   DECLARE @midpercent AS DECIMAL(3, 2) = 0.2
   DECLARE @upperpecent AS DECIMAL(3, 2) = 0.3
   --------------------return value
   DECLARE @payeamount AS MONEY

   ----------------for incomes below 215,000 ..no tax paid
   IF @grosspay <= @lowerlimit
   BEGIN
       set @payeamount = 0
   END

   -------for income less than or equal to 335,000, tax the difference between the income and 215,000 at 10%
   IF @grosspay <= @midlimit
   BEGIN
       SET @payeamount = (@grosspay - @lowerlimit) * @lowerpercent
   END

   -------for Income less than or equal to 410,000, tax the difference between the incomes and 335,000 at 20% plus 10,0000
   IF @grosspay <= @upperlimit
   BEGIN
       SET @payeamount = 10000 + (@grosspay - @midlimit) * @midpercent
   END

   --- for income less than or equal 10,000,000 tax the difference between the income and 410,000 at 30% plus 25,000/=
   IF @grosspay >= @newupperlimit
   BEGIN
       SET @payeamount = 25000 + (@grosspay - @upperlimit) * @upperpecent        
   END

   -----for income above 10,000,000 tax than difference between the income and 410,000 at 30% plus 25,000 plus 10% *(Income-10,000,000)
   IF @grosspay > @newupperlimit
   BEGIN
       SET @payeamount = 25000 + (@grosspay - @upperlimit) * @upperpecent
                         + (@grosspay - @newupperlimit) * @lowerpercent
   END

   RETURN @payeamount
END

Any suggestions to make it work as per the requirement?

Ronald

Upvotes: 0

Views: 33

Answers (1)

EzLo
EzLo

Reputation: 14209

Your return value is being set multiple times because your conditions aren't exclusive (and you are delaying the return value until last).

ALTER Function [dbo].[getPaye](@grosspay money)
returns money
as
begin
-------------different income  bands
Declare @lowerlimit money=215000
Declare @midlimit money=335000
Declare @upperlimit money=410000
declare @newupperlimit money=10000000
--------------different percentages
declare @lowerpercent as decimal(3,2)=0.1
declare @midpercent as decimal(3,2)=0.2
declare @upperpecent as decimal(3,2)=0.3
----------------for incomes below 215,000 ..no tax paid
     if @grosspay<=@lowerlimit
         begin
         RETURN 0
         end
-------for income less than or equal to 335,000, tax the difference between the income and 215,000 at 10%
     if @grosspay<=@midlimit
         begin
         RETURN (@grosspay-@lowerlimit)*@lowerpercent
         end
-------for Income less than or equal to 410,000, tax the difference between the incomes and 335,000 at 20% plus 10,0000
     if @grosspay<=@upperlimit
         begin
         RETURN 10000+(@grosspay-@midlimit)*@midpercent
         end
         ---for income less than or equal 10,000,000 tax the difference between the income and 410,000 at 30% plus 25,000/=
     if @grosspay<=@newupperlimit
         Begin
         RETURN 25000+(@grosspay-@upperlimit)*@upperpecent        
         end
     -----for income above 10,000,000 tax than difference between the income and 410,000 at 30% plus 25,000 plus 10% *(Income-10,000,000)
      RETURN 25000+(@grosspay-@upperlimit)*@upperpecent+(@grosspay-@newupperlimit)*@lowerpercent

end

Upvotes: 2

Related Questions