【解決方法】現在、変数にはカーソルが割り当てられていません。

[ad_1]

こんにちは、みんな、

カーソルを使用しようとしていますが、エラーが表示されます。変数には、割り当てられているにもかかわらず、現在カーソルが割り当てられていません。ご協力をよろしくお願いいたします。前もって感謝します。

私が試したこと:

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

解決策 1

構文が正しくありません:
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;

注意: カーソルは変数ではありません。名前が次で始まらない @、後の代入ではなく、宣言の一部として select ステートメントを含める必要があります。

SQL はセットベースの言語であることを意図していることにも注意してください。ほとんどの場合、カーソルを使用するよりも優れた解決策があります。

[ad_2]