locked
The cursor is already open error RRS feed

  • Question

  • User-1506965535 posted

    While executing the below procedure I get error as

    The cursor is already open.

    ALTER PROCEDURE [dbo].[Emp_Resign_Allocate_Leave]
    		@EmpCard_NO int,
    		@Month int,
    		@Year int
    AS 
    
    begin
    	
    	declare @actualMonth int
    	declare @actualYear int
    	declare @actuallastdate varchar(20)
    	declare @DAMonth int
    	declare @DAYear int
    	Declare @Final_LOP as numeric(5,2) 
    	Declare @Final_LOPDays as numeric(5,2) 
    	declare @checkRecord int
    	declare @NoOfDaysForDelayApproval numeric(5,2)
    	declare @Leave_Default numeric(5,2) 
    	declare @status varchar(10)
    	declare @resig_date datetime
    	declare @dt_of_leave datetime
    	declare @emp_name varchar(20)
    	declare @dt_of_join datetime
    	declare @emp_card_no numeric(9)
    	declare @comp_mkey numeric(9)
    	declare @pl_days numeric(5,2)
    	declare @pl_days_opening numeric(5,2)
    	declare @Month_Diff int
    	declare @month1 numeric(5,2)
    	declare @month2 numeric(5,2)
    	declare @month3 numeric(5,2)
    	declare @month4 numeric(5,2)
    	declare @month5 numeric(5,2)
    	declare @month6 numeric(5,2)
    	declare @month7 numeric(5,2)
    	declare @month8 numeric(5,2)
    	declare @month9 numeric(5,2)
    	declare @month10 numeric(5,2)
    	declare @month11 numeric(5,2)
    	declare @month12 numeric(5,2)
    	declare @pl_sum numeric(5,2)
    	declare @Total_days numeric(5,2)
    	declare @Days_worked numeric(5,2)
    	declare @actualleavedays numeric(5,2) 
    	declare @Final_PaidDayLop numeric(5,2)
    	declare @Opg_bal numeric(5,2)
    	declare @remaingPL as numeric(5,2)   
    	declare @DelayedMonth int
    	declare @DelayedYear int
    	declare @LOP_value  as numeric(5,2)  
    	declare @emp_type  varchar(2)
    	declare @emp_mkey int 
    	declare @PL_Opening_2015 numeric(10,2)
    	declare @TOTAL_PL_Allocation_2015 numeric(10,2)
    	declare @PL_Alloc_2015_Month1 numeric(10,2)
    	declare @PL_Alloc_2015_Month2 numeric(10,2)
    	declare @PL_Alloc_2015_Month3 numeric(10,2)
    	declare @PL_Alloc_2015_Month4 numeric(10,2)
    	declare @PL_Alloc_2015_Month5 numeric(10,2)
    	declare @PL_Alloc_2015_Month6 numeric(10,2)
    	declare @PL_Alloc_2015_Month7 numeric(10,2)
    	declare @PL_Alloc_2015_Month8 numeric(10,2)
    	declare @PL_Alloc_2015_Month9 numeric(10,2)
    	declare @PL_Alloc_2015_Month10 numeric(10,2)
    	declare @PL_Alloc_2015_Month11 numeric(10,2)
    	declare @PL_Alloc_2015_Month12 numeric(10,2)
    	declare @TOTAL_OL_Alloc_2015 numeric(10,2)
    	declare @OL_Alloc_2015_MONTH1 numeric(10,2)
    	declare @OL_Alloc_2015_MONTH2 numeric(10,2)
    	declare @OL_Alloc_2015_MONTH3 numeric(10,2)
    	declare @OL_Alloc_2015_MONTH4 numeric(10,2)
    	declare @OL_Alloc_2015_MONTH5 numeric(10,2)
    	declare @OL_Alloc_2015_MONTH6 numeric(10,2)
    	declare @OL_Alloc_2015_MONTH7 numeric(10,2)
    	declare @OL_Alloc_2015_MONTH8 numeric(10,2)
    	declare @OL_Alloc_2015_MONTH9 numeric(10,2)
    	declare @OL_Alloc_2015_MONTH10 numeric(10,2)
    	declare @OL_Alloc_2015_MONTH11 numeric(10,2)
    	declare @OL_Alloc_2015_MONTH12 numeric(10,2)
    	declare @Mkey int
    	declare @Entry_Sr_no int
    	declare @dcount int
    	DECLARE @sql NVARCHAR(MAX)
    	DECLARE @OnDate	DATETIME  
    	DECLARE @TotalRecord		numeric(5,2)
        DECLARE @date_of_joining        DATETIME
        DECLARE @setCount				INT 
    
    
    	set @Leave_Default=1.75
    
    	IF (@Month = 1)
    		BEGIN
    			set  @actualYear = @Year - 1
    			set	 @actualMonth = 12
    			set  @DelayedMonth = 11
    			set	 @DelayedYear = @Year - 1
    		END
    	ELSE
    		BEGIN
    			set @actualYear = @Year
    			set @actualMonth = @Month - 1
    
    			IF (@Month = 2)
    				BEGIN
    					set @DelayedMonth = 12
    					set	 @DelayedYear = @Year -1
    				END
    			Else
    				BEGIN
    					set @DelayedMonth = @actualMonth -1
    					set	@DelayedYear = @Year 
    				END
    		END
    
    	Print 'Actual Year ' + cast(@actualYear as VARCHAR)
    	Print 'Actual Month ' + Cast(@actualMonth as varchar)
    	Print 'Delayed Year ' + Cast(@DelayedYear as varchar)
    	Print 'Delayed Month' + Cast(@DelayedMonth  as varchar)
    	
    	DECLARE @DATE DATETIME;
    	--SET		@DATE = CAST(CAST(@actualYear AS VARCHAR)+'-'+CAST(@DelayedMonth AS VARCHAR)+'-'+ Cast(Day(DATEADD(DAY,-1,DATEADD(month,@DelayedMonth,DATEADD(year,@actualYear-1900,0)))) AS VARCHAR) AS DATETIME);
    	SET @DATE = CAST(convert(datetime,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)),103) AS DATETIME);
    	PRINT Convert(varchar(11),@DATE,103)
    
    	--set	@actuallastDate = CONVERT(CHAR(10),DATEADD(DAY, -1,DATEADD(MONTH, @actualMonth, DATEADD(YEAR,@actualYear - 2000, '20000101'))), 103)
    	--set @actuallastDate = convert(varchar(10),'1/'+Convert(Varchar,@actualMonth)+'/'+Convert(Varchar,@actualYear),103)
    	SET @actuallastDate = CAST(convert(datetime,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)),103) AS DATETIME);
    	print @actuallastDate
    			
    		BEGIN
    						Print 'Yes I am in the Monthly process'
    
    			SELECT @checkRecord = COUNT(Year) FROM p_leave_allocation where Year = @Year
    
    						IF(@checkRecord = 0)
    							--BEGIN
    							--		Print 'Kindly Run Annual process first for creation of Blank Records for New Year.'
    							--		RETURN 
    							--END	
    								
    							declare monthly_Allocate cursor  for
    							Select e.status,e.resig_date, dt_of_leave, e.emp_name,e.date_of_joining,  e.emp_card_no,
    									--datediff (month,e.date_of_joining,convert(datetime,@actuallastDate,103)) Month_Diff,
    									a.pl_days,pl_days_opening,
    									a.month1,a.month2,a.month3,a.month4,a.month5,a.month6,a.month7,a.month8,a.month9,a.month10,a.month11,a.month12,--actual_pl,
    									a.month1+a.month2+a.month3+a.month4+a.month5+a.month6+a.month7+a.month8+a.month9++a.month10+a.month11+a.month12 pl_sum
    							from p_leave_allocation a join emp_mst e on a.emp_card_no=e.emp_card_no 
    							where a.year=@actualYear
    									and (datediff(month,e.date_of_joining,convert(datetime,@actuallastDate,103)) >= 6 
    										 and datediff(month,e.date_of_joining,convert(datetime,@actuallastDate,103)) <= (36 + (@Month - 2) )
    									and (e.resig_date is null or  (e.dt_of_leave is not null  and e.dt_of_leave >= convert(datetime,@actuallastDate,103)))
    									--and e.status in ('A','S')
    --									and e.emp_card_no in (1852)
    									and e.comp_mkey in  
    									(Select  distinct m.comp_mkey from emp_mst e, company_mst c, P_Monthly_Must_Para_Hdr m where c.mkey = e.comp_mkey 
    									and m.comp_mkey  = e.comp_mkey and (m.process_date is not null 
    									and (convert(varchar,(getDate())-1,103)) = convert(varchar,m.process_date , 103))
    									))
    							order by 7,5 
    
    							OPEN monthly_Allocate fetch next
     							FROM monthly_Allocate into 
    	     					@status,@resig_date,@dt_of_leave,@emp_name, @dt_of_join,@emp_card_no,@pl_days,@pl_days_opening,
    							@month1,@month2,@month3,@month4,@month5,@month6,@month7,@month8,@month9,@month10,@month11,@month12,@pl_sum
    							--@Total_days,@Days_worked,@actualleavedays
    								
    							WHILE @@FETCH_STATUS = 0
    							BEGIN
    								set @actualleavedays = 0
    								set @Final_PaidDayLop = 0
    	
    								Print 'Faiz K.' + cast(@emp_card_no as varchar(10))
    
    								select	@Total_days =  Sum(total_day), @Days_worked = Sum(days_worked)       
    								from	emp_mon_day a      
    								where	a.emp_mkey =  @emp_card_no
    										and a.month = @actualMonth and Year = @actualYear
    								group by emp_mkey		
    
    								print @emp_card_no
    								print @Total_days
    								print @Days_worked
    
    								if(@Days_worked > 0)
    									BEGIN
    										--set @actualleavedays =((1.75) / @Total_days) * (@Days_worked)
    										Set @actualleavedays = (cast(@Leave_Default as numeric(18,2))/cast(@Total_days as numeric(18,2)))* cast(@Days_worked as numeric(18,2))      									
    									END 
    
    								print @actualleavedays	
    
    								SET @sql = '';
    
    								--SET @sql = N'update p_leave_allocation  
    								--			SET MONTH'+CAST(@actualMonth AS VARCHAR(20)) +' = ' + CAST(@actualleavedays AS VARCHAR(10))+
    								--			' WHERE YEAR = '+CAST(@actualYear AS VARCHAR(20))+ 'and emp_card_no = '+ Cast(@emp_card_no AS VARCHAR(100))+'';
    
    								PRINT @sql 
    								EXEC SP_EXECUTESQL @sql;
    
    								BEGIN	
    										declare @totaldaysofmonth decimal(9,2)
    										set @totaldaysofmonth =  DAY(DATEADD(ms,-2,DATEADD(MONTH, DATEDIFF(MONTH,0,@DATE)+1,0)))
    
    										select @NoOfDaysForDelayApproval=isnull(sum(Total_Days),0.00) 
    										from XXACL_EMP_DELAY_APPROVAL_V  
    										where DYear = @DelayedYear  and DMonth = @DelayedMonth
    												and CardNo = @emp_card_no
    
    										print	@NoOfDaysForDelayApproval
    
    										declare @DelayleaveAllocate decimal(9,2)								
    										set		@DelayleaveAllocate = ((1.75 / @totaldaysofmonth) * @NoOfDaysForDelayApproval)					  						
    
    										print	@DelayleaveAllocate
    										
    										SET @sql = '';
    										print 'Yes 01'
    										print @DelayedMonth
    										
    										declare @strmon varchar(100) 
    										set @strmon= 'cast(month'+ CAST(@DelayedMonth AS VARCHAR(100)) + ' as numeric(10,2)) + cast(' + cast(@DelayleaveAllocate as varchar(50)) +' as numeric(10,2))'
    
    										IF(@Month > 2)
    											----- Update delayed approval in Opening ------------------------------
    											--BEGIN
    											--	SET @sql = 'update p_leave_allocation SET MONTH' + CAST(@DelayedMonth AS VARCHAR(100)) + ' = ' + @strmon + 
    											--		' WHERE YEAR = '+CAST(@actualYear AS VARCHAR(100))+ ' and emp_card_no = '+ Cast(@emp_card_no AS VARCHAR(100))+'';
    											--END 
    --										ELSE
    --											BEGIN
    --												SET @sql = 'update p_leave_allocation SET PL_Days_Opening  = ' + (@pl_days_opening + @strmon) + 
    --													' WHERE YEAR = '+CAST(@actualYear AS VARCHAR(100))+ ' and emp_card_no = '+ Cast(@emp_card_no AS VARCHAR(100))+'';
    --											END 	
    
    
    										PRINT @sql 
    										 
    										EXEC SP_EXECUTESQL @sql;
    										
    										END
    		
    										print	@DelayleaveAllocate
    															
    
    								--Update p_leave_allocation 
    								--set Total_Alloc = Month1+Month2+Month3+Month5+Month5+Month6+Month7+Month8+Month9+Month10+Month11+Month12
    								--where year = @actualYear and emp_card_no = @emp_card_no
    
    								--Update p_leave_allocation 
    								--set PL_Days = Total_Alloc
    								--where year =  @actualYear  and emp_card_no = @emp_card_no
    
    								--Update p_leave_allocation 
    								--set Actual_PL = Total_Alloc
    								--where year =  @actualYear  and emp_card_no = @emp_card_no
    
    
    END	  
    END
    END	
    
    
    
    --  exec Emp_Resign_Allocate_Leave 2277, 4, 2016

    I dont know why, kindly help

    Wednesday, April 27, 2016 7:11 AM

Answers

  • User16166972 posted

    try adding this

    IF CURSOR_STATUS('global','myCursor')>=-1
    BEGIN
     DEALLOCATE myCursor
    END
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 27, 2016 7:55 AM

All replies

  • User16166972 posted

    try adding this

    IF CURSOR_STATUS('global','myCursor')>=-1
    BEGIN
     DEALLOCATE myCursor
    END
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 27, 2016 7:55 AM
  • User753101303 posted

    Hi,

    Even the first time? Could it be that you don't close the cursor causing this error when you call this procedure for the 2nd time?

    Wednesday, April 27, 2016 7:56 AM