lundi 30 mai 2016

IF ELSE in SQL Stored Procedure

I'm trying to accomplish the IF ELSE statement in SQL Stored Procedure but it seems that it wont follow the condition. I tried to declare an 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

Aucun commentaire:

Enregistrer un commentaire