Cursor does not exist?? Yes it does!

[cjkenworthy]

Any ideas why I kepe getting the persistant error: "Error 16916: A cursor with the name 'RsCursor' does not exist"

I have declared the cursor??

CREATE PROCEDURE NoTaskOwner

AS

DECLARE RsCursor CURSOR FOR

SELECT case_id
FROM FaultLog
WHERE AssignedTo IS NULL

FOR READ ONLY

OPEN RsCursor
GO

FETCH NEXT FROM RsCursor

WHILE (@@FETCH_STATUS <> -1)

BEGIN
FETCH NEXT FROM RsCursor
END
GO

CLOSE RsCursor
GO
DEALLOCATE RsCursor
GO

 

[AtomicWedgie]

when do you get the error?

 

[vongrunt]

1. Don't use GO all over the place, only at the end.
2. FETCH NEXT FROM RsCursor INTO @some_variable

 

[cjkenworthy]

I cleaned the code up a bit (see below) but now the procedure doesn't even do anything, even though it executes sucessfully.

I want it to retrieve some records and loop through them sending an email for each record. I know there are 4 records which this should pull out, but testing @@FETCH_STATUS give '-1'

Any ideas? Thanks

CREATE PROCEDURE NoAssignedTo AS

DECLARE @varCase_id VARCHAR(100)
DECLARE @MessageBody VARCHAR(100)

/* Declare and open a cursor */
DECLARE rsCursor CURSOR
FOR
SELECT case_id FROM dbo.FaultLog WHERE AssignedTo IS NULL
FOR READ ONLY
OPEN rsCursor

/* Loop through rows */
WHILE @@FETCH_STATUS = 0
BEGIN

FETCH NEXT FROM rsCursor INTO @varCase_id

SET @MessageBody = 'Task case number ' + @varCase_id + ' has not been assigned'
EXEC master.dbo.xp_sendmail 'chrisk@uncl.net', @MessageBody, @Subject = 'A task has not been assigned'

END

/* Close and deallocate cursor */
CLOSE rsCursor
DEALLOCATE rsCursor

 

[vongrunt]

Also make initial fetch:

[b]FETCH NEXT FROM blah INTO...[/b]
WHILE @@FETCH_STATUS = 0
BEGIN
	...
	FETCH NEXT FROM blah INTO...
END