locked
Incorrect syntax near 'END RRS feed

  • Question

  • User-1506965535 posted

    I have a procedure which gives me error as 

    Msg 102, Level 15, State 1, Procedure AnnualLeaveAllocation2016, Line 74

    Incorrect syntax near 'END'.

    I dont know why, below is my code

    ALTER PROCEDURE [dbo].[AnnualLeaveAllocation2016]
    @OLType varchar(3),
    @Year int
    
    AS
      
         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,0,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
          
          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 get_leavedata 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 get_leavedata fetch next from get_leavedata 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  
       select @remaingPL= (isnull(PL_Days,0.00)+isnull(PL_Days_Opening,0.00)) - 
       (isnull(a.OL_Month1,0.00)+isnull(a.OL_Month2,0.00)+
       isnull(a.OL_Month3,0.00)+isnull(a.OL_Month4,0.00)+
       isnull(a.OL_Month5,0.00)+isnull(a.OL_Month6,0.00)+
       isnull(a.OL_Month7,0.00)+isnull(a.OL_Month8,0.00)+
       isnull(a.OL_Month9,0.00)+isnull(a.OL_Month10,0.00)+
       isnull(a.OL_Month11,0.00)+isnull(a.OL_Month12,0.00)) 
       from p_leave_allocation_14122015 a where  a.Year = cast(@LYears as numeric)-1 and a.Emp_card_no = @EmpCardNo  
       print  @remaingPL  
    
      ----- Remaining PL for less than 0 -----------------------
       if(@remaingPL<0)  
        begin  
         set @remaingPL=0  
        end   
       print 'remainingpl'  
       print @remaingPL  
       --update select @Final_Lop = sum(isnull(total_absent,0)) from Emp_mon_day  where  year in  (@LOPYear) and emp_mkey = @EmpCardNo and delete_flag='N' set PLDayO =  isnull(@remaingPL,0.00) where Emp_Card_No = @EmpCardNo  
       update p_leave_allocation_14122015 set PL_Days =  isnull(@remaingPL,0.00) where Emp_Card_No = @EmpCardNo  
     
          
         END

    Please suggest

    Tuesday, December 15, 2015 5:20 AM

Answers

  • User1577371250 posted

    Hi,

    You are missing the END for the below while loop. 

     while @@fetch_status=0         
      begin    
       declare @remaingPL as numeric(10,2)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 15, 2015 5:23 AM

All replies

  • User1577371250 posted

    Hi,

    You are missing the END for the below while loop. 

     while @@fetch_status=0         
      begin    
       declare @remaingPL as numeric(10,2)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 15, 2015 5:23 AM
  • User-1506965535 posted

    Thanks a lot brother.. that worked :)

    Tuesday, December 15, 2015 5:44 AM
  • User-1506965535 posted

    One more thing here,

    When I am executing the above Stored procedure, I am getting message as

    The wait operation timed out

    Tuesday, December 15, 2015 5:54 AM