Using Cursor

mike55

mike55

Well-known member
Joined
Mar 26, 2004
Messages
726
Location
Ireland
Hi all

I am trying to use cursors in a database procedure, here is the cursor code that I am using:
Code: 
	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 said:
Try this instead
Code: 
WHERE CURRENT OF Manager_Cursor;
Click to expand...
Many thanks for the reply, here is what I used in the end:
Code: 
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.
 
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 hasnt been tested, if it works try comparing both vesions with Query Analyzers Show Execution Plan function.

Code: 
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
 
Back
Top