Using Cursor

[mike55]

Hi all

 

I am trying to use cursors in a database procedure, here is the cursor code that I am using:

DECLARE Manager_Cursor CURSOR FOR
SELECT Manager_ID FROM Managers WHERE Org_ID = @OrgID
OPEN Manager_Cursor;
FETCH NEXT FROM Manager_Cursor;
WHILE @@FETCH_STATUS = 0
	BEGIN
		DELETE FROM Groups_Reference
		WHERE Manager_ID = CURRENT OF Manager_Cursor
		FETCH NEXT FROM Manager_Cursor;
	END
CLOSE Manager_Cursor;
DEALLOCATE Manager_Cursor;

 

However I am getting the following error:

Incorrect syntax near the keyword 'current'

 

Any suggestions.

 

Mike55

[IUnknown]

			DELETE FROM Groups_Reference
		WHERE Manager_ID = CURRENT OF Manager_Cursor

Try this instead

WHERE CURRENT OF Manager_Cursor;

[mike55]

Try this instead

WHERE CURRENT OF Manager_Cursor;

Many thanks for the reply, here is what I used in the end:

DECLARE @Manager nvarchar (10)

DECLARE Manager_Cursor CURSOR FOR
SELECT Manager_ID FROM Managers WHERE Org_ID = @OrgID
OPEN Manager_Cursor;
FETCH NEXT FROM Manager_Cursor INTO @Manager;
WHILE @@FETCH_STATUS = 0
	BEGIN
		DELETE FROM Groups_Reference
		WHERE Manager_ID = @Manager
		FETCH NEXT FROM Manager_Cursor INTO @Manager;
	END
CLOSE Manager_Cursor;
DEALLOCATE Manager_Cursor;

 

Mike55.

[PlausiblyDamp]

It looks as though you are deleting records from one table (groups_reference) based on the results of a query on another table (Managers) - is that right?

 

If so you may want to look at the functionality provided by DELETE FROM FROM as cursors will often provide worse performance and are not an implicit transaction - your code above could cause problems if a system failure occured during the WHILE loop; you would need to wrap the function inside a BEGIN TRAN ... COMMIT block to ensure reliability.

 

Somethng like the following should do it - it hasn't been tested, if it works try comparing both vesions with Query Analyzer's 'Show Execution Plan' function.

 

DELETE FROM Groups_Reference
FROM Managers AS M INNER JOIN Groups_Reference  as GR
on M.Manager_ID = GR.Manager_ID
WHERE Org_ID = @OrgID

[mike55]

Many thanks, will try out your suggestion.

 

Mike55.