The variable does not currently have a cursor allocated to it.

Question

0.00/5 (No votes)

See more:

Hi All ,

I am trying to use a cursor and its showing error The variable does not currently have a cursor allocated to it even though its allocated . Any help will be really appreciated . Thanks in advance .

What I have tried:

SQL

<pre>DECLARE @firstday1 Date = null,
	@Lastday1 Date = null,
	@RequestType1 nvarchar = null,
	@EmployeeIdRequest1 int = null


SELECT @firstday1 = FORMAT(PeriodFrom,'yyyy-MM-dd') , @Lastday1 = FORMAT(PeriodTo,'yyyy-MM-dd') ,@RequestType1 = TRIM(RequestType),@EmployeeIdRequest1 = EmployeeID  FROM MST_TBL_Approvalrequests WHERE Autoid = 	@ApprovalReqID;


 DECLARE @Enumerator1 CURSOR 
		SET @Enumerator1 = CURSOR  FAST_FORWARD FOR
				 with dates_CTE1 (date) as (
						select @firstday1 
						Union ALL
						select DATEADD(day, 1, date)
						from dates_CTE1
						where date < @Lastday1
						)	
				select  date  FROM dates_CTE1
				OPTION (maxrecursion 365);
		OPEN @Enumerator1
		DECLARE @LoopDate1 date
	
		WHILE (1=1)
				BEGIN
					FETCH NEXT FROM @Enumerator1 into @LoopDate1	
						IF (@@FETCH_STATUS <> 0) break

	   BEGIN
	   if exists(SELECT AutoId from PPMS_TBL_AttendanceMaster where FORMAT(date,'yyyy-MM-dd')=@LoopDate1 and EmployeeID = @EmployeeIdRequest1 )            
       BEGIN 
        UPDATE PPA SET 
             PPA.PresentStatus = case when (MSTA.RequestTypeID = 10 and PPA.PresentStatus ='Work From Home') then 'Work From Home' 
									  when (MSTA.RequestTypeID = 10 and PPA.PresentStatus NOT IN ('Work From Home')) THEN 
									                                                             case when ((CAST(PPA.AdjustOuttime AS TIME)) < (CAST(TSH.ShiftEndTime AS TIME))) then 'Half Day' else 'Present' end
									 when MSTA.RequestTypeID = 12 then 'Present' else MSTA.RequestType end 
		,PPA.IsApproved=1 
		,PPA.InTime = case when MSTA.RequestTypeID = 10 then PPA.AdjustInTime else InTime end
		,PPA.OutTime= case when MSTA.RequestTypeID = 10 then PPA.AdjustOuttime else OutTime end 
		,StatusAlert = case when MSTA.RequestTypeID = 10 then (CASE WHEN (((-1) * DATEDIFF(MINUTE, TSH.ShiftStartTime,   CONVERT(VARCHAR(8),PPA.AdjustInTime,108))) >= 0) THEN 'Green' ELSE 'Red' END)  else  StatusAlert end
		,LateHours = case when MSTA.RequestTypeID = 10 then (CASE WHEN  ((-1) * DATEDIFF(MINUTE, TSH.ShiftStartTime,   CONVERT(VARCHAR(8),PPA.AdjustInTime,108)) < 0) THEN (DATEDIFF(MINUTE, TSH.ShiftStartTime, CONVERT(VARCHAR(8),PPA.AdjustInTime,108))) ELSE '' END) else LateHours end  
		from MST_TBL_Approvalrequests MSTA
		INNER JOIN  PPMS_TBL_AttendanceMaster PPA  ON MSTA.EmployeeID = PPA.EmployeeID
		INNER JOIN  PPMS_TBL_EmployeeMaster PTE ON PPA.EmployeeID = PTE.Autoid
		left JOIN TBL_Shifts TSH ON TSH.Autoid=PTE.ShiftCatagoryID
		WHERE ApprovedStatus = 'Approved' and MSTA.Autoid = @ApprovalReqID
		and (date BETWEEN PeriodFrom  AND PeriodTo) and MSTA.EmployeeID = @EmployeeIdRequest1   AND  RequestTypeID in (1,2,3,4,5,6,7,8,9,10)

		END
		ELSE
		BEGIN
	    INSERT INTO [PPMS_TBL_AttendanceMaster](EmployeeID, Name, [Date], AttendanceStatus,PresentStatus)
						SELECT AutoID,Name, @LoopDate1,'Waiting for Review',@RequestType
						FROM [PPMS_TBL_EmployeeMaster]  where AutoID = @EmployeeIdRequest1 
						and PPMS_TBL_EmployeeMaster.active=1 
        END

        end
        CLOSE @Enumerator1
		DEALLOCATE @Enumerator1

Posted 11-May-23 20:23pm

Member 12926744

Updated 11-May-23 21:27pm

Add a Solution

Comments

CHill60 12-May-23 4:21am

As @Richard-Deeming said "There is almost always a better solution than using a cursor" - in this case I believe you just need to use an inner join to your dates_CTE1 A and PPMS_TBL_AttendanceMaster B ON A.[date] = B.[date] -- you may need to cast the table date to type date if the column is datetime.
Then join that lot to the rest of your query and use set-based updates/inserts.
You might find my article useful - Processing Loops in SQL Server[^]

Member 12926744 16-May-23 5:59am

Thank you will try that way too .

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2023-05-11T21:27:00

Your syntax is incorrect:
DECLARE CURSOR (Transact-SQL) - SQL Server | Microsoft Learn[^]

SQL

DECLARE Enumerator1 CURSOR FAST_FORWARD FOR
WITH dates_CTE1 (date) As (
    SELECT @Firstday1
    UNION ALL
    SELECT DateAdd(day, 1, date)
    FROM dates_CTE1
    WHERE date < @Lastday1
)
SELECT date
FROM dates_CTE1
OPTION (maxrecursion 365);

OPEN Enumerator1;

DECLARE @LoopDate1 date;
WHILE (1 = 1)
BEGIN
    FETCH NEXT FROM Enumerator1 INTO @LoopDate1;
    If @@FETCH_STATUS <> 0 BREAK;
    ...
END;

CLOSE Enumerator1;
DEALLOCATE Enumerator1;

NB: Cursors are not variables. The names do not start with @, and you need to include the select statement as part of the declaration, not by a later assignment.

Also remember that SQL is intended to be a set-based language. There is almost always a better solution than using a cursor.