FrostyPinky FrostyPinky - 6 months ago 10
SQL Question

IF ELSE in SQL Server stored procedure

I'm trying to accomplish the

IF ELSE
statement in a SQL Server stored procedure but it seems that it won't follow the condition. I tried to declare a static value for me to check it but its still the same. My problem is it wont go to
ELSE
even if the condition is wrong

Here's the code:

ALTER PROCEDURE [dbo].[Amount_Computation]
(@OfficeID int,
@AccountID int,
@Amount int,
@NoOfMonths int,
@Percentage int,
@isRoundOf int,
@MaxAmount int,
@EmployeeType int)
AS
declare @TotalAmount table(TotalAmount int)
declare @Casual table(CasualSalary int, OfficeID int)
declare @Regular table(RegularSalary int, OfficeID int)

declare @basic int
BEGIN
SELECT
@Amount = 1, @OfficeID = 72,
@AccountID = 733, @Amount = 0,
@NoOfMonths = 12, @Percentage = 1.25,
@isRoundOf = 1, @MaxAmount = 35000,
@EmployeeType = 1

IF(@Amount = 0)
BEGIN
INSERT INTO @Casual
SELECT
CAST(((select LEFT(CONVERT(nvarchar,CAST((Case when (basic * 22 > @MaxAmount) then @MaxAmount ELSE Basic * 22 END) AS INT)),LEN(CONVERT(nvarchar,CAST((Case when (basic * 22 > @MaxAmount) then @MaxAmount ELSE Basic * 22 END) AS INT))) - 3)) + '000' ) AS INT ) * @Percentage / 100 * @NoOfMonths as Casual,
a.OfficeID
FROM
pmis.dbo.vw_RGPermanentAndCasual as a
LEFT JOIN
ifmis.dbo.tbl_R_BMSOffices as b ON b.PMISOfficeID = a.OfficeID
WHERE
a.OfficeID = @OfficeID AND a.EmploymentGroup = '2'

INSERT INTO @Regular
SELECT
CAST(((select LEFT(CONVERT(nvarchar,CAST((Case when (basic > @MaxAmount) then @MaxAmount ELSE Basic END) AS INT)),LEN(CONVERT(nvarchar,CAST((Case when (basic > @MaxAmount) then @MaxAmount ELSE Basic END) AS INT))) - 3)) + '000' ) AS INT ) * @Percentage / 100 * @NoOfMonths as Regular,
a.OfficeID
FROM
pmis.dbo.vw_RGPermanentAndCasual as a
LEFT JOIN
ifmis.dbo.tbl_R_BMSOffices as b ON b.PMISOfficeID = a.OfficeID
WHERE
a.OfficeID = @OfficeID AND a.EmploymentGroup = '1'

INSERT INTO @TotalAmount
SELECT
SUM(CasualSalary) + SUM(RegularSalary)
FROM
@Casual as a
LEFT JOIN
@Regular as b ON b.OfficeID = a.OfficeID
END
ELSE IF(@Amount = 1)
BEGIN
INSERT INTO @TotalAmount
SELECT SUM(CasualSalary) as ELSE_IF
FROM @Casual
END
END

/**SELECT CasualSalary FROM @Casual
SELECT RegularSalary FROM @Regular **/
SELECT TotalAmount FROM @TotalAmount


EDIT: Select @Amount should be

SELECT @Amount = 1,@OfficeID = 72,@AccountID = 733, @NoOfMonths = 12, @Percentage = 1.25, @isRoundOf = 1, @MaxAmount = 35000, @EmployeeType = 1

Answer

As you noticed yourself - @Amount is listed twice in your SELECT list, with two distinct values.

Easy fix. :)