IF ELSE not working as required in Procedure

Question

User-1506965535 posted

I  have a procedure where I want to check the condition and proceed accordingly. The condition is working properly for "OLType = OL"

but not working for "OLType = PL".

The query is executing but the data is not getting inserted for that.Below is the query

ALTER PROCEDURE [dbo].[AnnualLeaveAllocation2016]
@OLType varchar(3),
@Year int
AS
	--IF @OLType
     IF (@OLType = 'OL')
		 BEGIN
			   INSERT INTO p_leave_allocation_14122015(Mkey,Entry_Sr_no,Emp_doj, Comp_mkey,FDepartment_Id,
				FModule_Id,Month,Year, Emp_mkey, Emp_card_no, U_Datetime, Delete_Flag,Month12,PL_Days,
				OL_Days,PL_Days_Opening,Month1,Month2,Month3,Month4,Month5,Month6,Month7,
				Month8,Month9,Month10,Month11,Total_Alloc,OL_Month1,OL_Month2,OL_Month3,OL_Month4,
				OL_Month5,OL_Month6,OL_Month7,OL_Month8,OL_Month9,OL_Month10,OL_Month11,OL_Month12,
				OL_Alloc,PLUtil_Month1,PLUtil_Month2,PLUtil_Month3,PLUtil_Month4,PLUtil_Month5,
				PLUtil_Month6,PLUtil_Month7,PLUtil_Month8,PLUtil_Month9,PLUtil_Month10,
				PLUtil_Month11,PLUtil_Month12,OL_FirstHalf,OL_SecHalf,OLUtil_FirstHalf,
				OLUtil_SecHalf,CO_Days_Opening,COUtil_Month1,COUtil_Month2,COUtil_Month3,
				COUtil_Month4,COUtil_Month5,COUtil_Month6,COUtil_Month7,COUtil_Month8,COUtil_Month9,
				COUtil_Month10,COUtil_Month11,COUtil_Month12,Actual_PL,Actual_OL,User_Id,
				PL_Others_SP,NextLeve_Year,LOPRD_Year, Remarks)
				SELECT ROW_NUMBER() OVER(ORDER BY lev.MKey) + 1643,ROW_NUMBER() OVER(ORDER BY Entry_Sr_no) + 898, emp.Date_Of_Joining, lev.Comp_mkey,FDepartment_Id, FModule_Id,1,@Year,Emp_mkey,lev.Emp_card_no,lev.U_Datetime,
				lev.Delete_Flag,Month12, 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
				0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,@Year + 1,0, 'New Executive 2016'
				FROM p_leave_allocation lev LEFT JOIN Emp_mst emp
					 ON Emp.Emp_card_no = lev.Emp_card_no and lev.Year = (@Year -1)
				WHERE emp.Status in ('A', 'S')  and 
					  YEAR(emp.Date_Of_Joining) <= @Year
		 END
     
     
     ELSE IF (@OLType = 'PL')
     
				BEGIN
						   declare @EmpCardNo varchar(20) ,@Emp_Doj  varchar(20),@LYears varchar(10)
								declare  @pl1 varchar(10),@pl2 varchar(10), @pl3 varchar(10),@pl4 varchar(10), @pl5 varchar(10)  
								declare @pl6 varchar(10), @pl7 varchar(10),@pl8 varchar(10), @pl9 varchar(10),@pl10 varchar(10)  
								declare @pl11 varchar(10),@pl12 varchar(10),@plTotal varchar(10),@NextLeaveYear numeric (5,0)  
			
					  DECLARE EmpLeaveAlloc_Cursor CURSOR FOR SELECT Emp_card_no,Emp_doj, Year, Month1, Month2,Month3,Month4,Month5,
															Month6,Month7,Month8,Month9,Month10,Month11,Month12,
															Total_Alloc, NextLeve_Year from p_leave_allocation_14122015
					open EmpLeaveAlloc_Cursor fetch next 
					from EmpLeaveAlloc_Cursor into @EmpCardNo,@Emp_Doj,@LYears,@pl1,@pl2,@pl3,@pl4,@pl5,@pl6,@pl7,@pl8,@pl9,@pl10,@pl11,@pl12,@plTotal, @NextLeaveYear

				WHILE @@FETCH_STATUS = 0

				begin    
					declare @remaingPL as numeric(10,2)   
					set @remaingPL=0  
					   
					--print @EmpCardNo 
					--Print @year 
					   
						select @remaingPL= (isnull(PL_Days,0.00)+isnull(PL_Days_Opening,0.00)) - 
						(isnull(a.PLUtil_Month1,0.00)+isnull(a.PLUtil_Month2,0.00)+
						isnull(a.PLUtil_Month3,0.00)+isnull(a.PLUtil_Month4,0.00)+
						isnull(a.PLUtil_Month5,0.00)+isnull(a.PLUtil_Month6,0.00)+
						isnull(a.PLUtil_Month7,0.00)+isnull(a.PLUtil_Month8,0.00)+
						isnull(a.PLUtil_Month9,0.00)+isnull(a.PLUtil_Month10,0.00)+
						isnull(a.PLUtil_Month11,0.00)+isnull(a.PLUtil_Month12,0.00)) 
						from p_leave_allocation a where  a.Year = (@Year- 1) and a.Emp_card_no = @EmpCardNo  
						   
					--print  @remaingPL  
					--print 'card no : ' + @EmpCardNo
					
					----- Remaining PL for less than 0 -----------------------
					
					if(@remaingPL<0)  
						begin  
							set @remaingPL=0  
						end   
					
					update p_leave_allocation_14122015 set PL_Days_Opening =  isnull(@remaingPL,0.00) 
					where Emp_Card_No = @EmpCardNo  
					
					if(datediff(MONTH,@Emp_Doj, Convert(datetime, getdate(), 103)) > 36)
						BEGIN
							   Update p_leave_allocation_14122015 set 
							   Month1 = 1.75, Month2 = 1.75,Month3 = 1.75,Month4 = 1.75,Month5 = 1.75,Month6 = 1.75,Month7 = 1.75,
							   Month8 = 1.75,Month9 = 1.75,Month10 = 1.75,Month11 = 1.75,Month12 = 1.75,
							   Total_Alloc=21, PL_Days=21, Actual_PL = 21, U_Datetime=getdate()
							   where Emp_Card_No = @EmpCardNo and Year = @Year
						END  
						
					fetch next 
					from EmpLeaveAlloc_Cursor into @EmpCardNo,@Emp_Doj,@LYears,@pl1,@pl2,@pl3,@pl4,@pl5,@pl6,@pl7,@pl8,@pl9,@pl10,@pl11,@pl12,@plTotal, @NextLeaveYear
				END			   
					close EmpLeaveAlloc_Cursor 
					deallocate EmpLeaveAlloc_Cursor 
	   END

any reason, why it is not working ?

Answer 1

User-693045842 posted

Hi ,

Please check the second branch is correct .

With Regards,

Answer 2

User-219423983 posted

Hi nadeem157,

I  have a procedure where I want to check the condition and proceed accordingly. The condition is working properly for "OLType = OL"

but not working for "OLType = PL".

The query is executing but the data is not getting inserted for that.

I suggest you could first remove the variables that are not be used in the batch “ELSE IF” of your code to move your code be easy to check.

Then, as your second batch has two “update” statement, you should debug your code step to step to check where the data not be inserted, especially the value of “@Emp_Doj”.

In the first “Update” statement of the “else if” batch, the “@remaingPL” would always not be null. So, maybe you should change the magic of this sentence.

Best Regards,

Weibo Zhang