user3482471 user3482471 - 1 month ago 9
SQL Question

SQL Cursor Error - Does not currently have a cursor allocated to it

I have been attempting to create a cursor that will pass multiple variables and then eventually the select that is within the cursor will eventually become an update. But for my preliminary stages It will just be a select.

However when i am running this i am receiving an error message multiple times.
"The variable '@Cur' does not currently have a cursor allocated to it"

Please can I have some assistance with the SQL as i am not sure what is going wrong with it? Im sure it is something simple but I am unable to figure this out.

PS. ignore the column numbers and table numbers this was for example purposes.

DECLARE
@VAL1 DATETIME,
@VAL2 INT,
@VAL3 INT

SELECT * INTO #TEMP1 FROM
(SELECT
BS.COL,
BS.COL,
BS.COL
FROM TABLEB
JOIN TABLe1 HO (NOLOCK) ON HO.COL1 = B.COL1
JOIN TABLE2 SPB (NOLOCK) ON B.COL2 = SPB.COL2
JOIN TABLE3 BS (NOLOCK) ON BS.COL1 = SPB.COL1
JOIN TABLE4 SPS (NOLOCK) ON SPS.COL1 = BS.COL1
JOIN TABLE5 P (NOLOCK) ON P.COL1 = BS.COL1
JOIN TABLE6 (NOLOCK) ON PS.COL1 = SPS.COL1
WHERE BS.col1 = 'N'
AND SPB.COL1 = 'N'
AND SPS.COL2 = 'N'
AND PS.COL3 = 'N'
AND P.COL4 = 'N'
AND B.COL5 = 'N'
AND BS.COL5 IS NULL
AND PS.COL6 IS NOT NULL
AND PS.COL6 IS NULL
AND PS.COL7 <= DATEADD(DAY,-10,GETDATE())
AND BS.COL8 IS NOT NULL) Y

DECLARE @Cur CURSOR SELECT
CASE WHEN
BS.COL1 IS NOT NULL
THEN ISNULL((SELECT COL2 FROM TABLE1 WHERE COL2 IN (SELECT COL1 FROM #TEMP1)),PS.COL2)
WHEN PS.COL2 <> SPS.COL3 THEN SPS.COL5
ELSE PS.COL3 END AS 'DATE',
BS.COL3,
BS.COL2
FROM TABLEB
JOIN TABLE1 HO (NOLOCK) ON HO.COL1 = B.COL1
JOIN TABLE2 SPB (NOLOCK) ON B.COL2 = SPB.COL2
JOIN TABLE3 BS (NOLOCK) ON BS.COL1 = SPB.COL1
JOIN TABLE4 SPS (NOLOCK) ON SPS.COL1 = BS.COL1
JOIN TABLE5 P (NOLOCK) ON P.COL1 = BS.COL1
JOIN TABLE6 (NOLOCK) ON PS.COL1 = SPS.COL1
WHERE BS.col1 = 'N'
AND SPB.COL1 = 'N'
AND SPS.COL2 = 'N'
AND PS.COL3 = 'N'
AND P.COL4 = 'N'
AND B.COL5 = 'N'
AND BS.COL5 IS NULL
AND PS.COL6 IS NOT NULL
AND PS.COL6 IS NULL
AND PS.COL7 <= DATEADD(DAY,-10,GETDATE())
AND BS.COL8 IS NOT NULL
AND PS.COL 3 <= CURRENT_TIMESTAMP


OPEN @Cur
FETCH NEXT
FROM @Cur INTO @VAL1 ,@VAL2,@VAL3
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT * FROM TABLE1 WHERE COL1 = @VAL2
FETCH NEXT FROM @Cur INTO @VAL1,@VAL2,@VAL3
END
CLOSE @Cur
DEALLOCATE @Cur
DROP TABLE #TEMP1

Answer

You are missing the FOR in "DECLARE @Cur CURSOR SELECT" . Change this to "DECLARE @Cur CURSOR FOR SELECT"