FrostyPinky FrostyPinky - 6 months ago 7
SQL Question

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


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. :)

Comments