locked
IF ELSE not working as required in Procedure RRS feed

  • 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 ?

    Thursday, December 17, 2015 6:11 AM

Answers

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 29, 2016 8:07 AM

All replies

  • User-693045842 posted

    Hi ,

    Please check the second branch is correct .

    With Regards,

    Friday, December 18, 2015 6:30 AM
  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 29, 2016 8:07 AM