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