SP NOT WORKING - HELP!

[hinchdog]

This SP finds a record whose "current" column is true and set it to false, then moves to the next record and sets its "current" to true. However, if there is no next record (it's at the end of the table), then it is supposed to move to the first record and set "current" to true.

The SP works fine until it gets to the last record. I can't get the cusors to recognize that it at the end using @@FETCH_STATUS=2. What the heck am I doing wrong?? Very perplexed here!


CREATE PROC sp_ChangeAdmin
AS
BEGIN
DECLARE @ThisID INT
DECLARE @ThisCurrent BIT
DECLARE ThisAdmin CURSOR FOR
SELECT admin_id, admin_current
FROM admin

OPEN ThisAdmin
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH FROM ThisAdmin INTO @ThisID, @ThisCurrent
IF @ThisCurrent = 1
BREAK
ELSE
CONTINUE
FETCH NEXT FROM ThisAdmin
END
-- change current admin to normal
UPDATE admin SET admin_current=0 WHERE admin_id=@ThisID
-- get next admin
FETCH NEXT FROM ThisAdmin
-- check if end of records, if so start over
IF @@FETCH_STATUS=2
FETCH FIRST FROM ThisAdmin INTO @ThisID, @ThisCurrent
UPDATE admin SET admin_current = 1 WHERE admin_id = @ThisID

CLOSE ThisAdmin
DEALLOCATE ThisAdmin
END

 

[hinchdog]

Well, as noone responded, I was forced to figure it out by myself. Here's the working code incase anyone is interested

CREATE PROC sp_ChangeAdmin
AS
BEGIN
DECLARE @ThisID INT
DECLARE @ThisCurrent BIT
DECLARE ThisAdmin SCROLL CURSOR FOR
SELECT admin_current FROM admin FOR UPDATE

OPEN ThisAdmin
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM ThisAdmin INTO @ThisCurrent
IF @ThisCurrent = 1
BEGIN
-- change current admin to normal
UPDATE admin SET admin_current=0 WHERE CURRENT OF ThisAdmin
FETCH FROM ThisAdmin
BREAK
END
ELSE
CONTINUE
END

--check if not end of table (0), if not move next,
--if so start over
IF @@FETCH_STATUS= 0
FETCH NEXT FROM ThisAdmin
ELSE
FETCH FIRST FROM ThisAdmin
UPDATE admin SET admin_current = 1 WHERE CURRENT OF ThisAdmin

CLOSE ThisAdmin
DEALLOCATE ThisAdmin
END

 

[foxdev]

I can't see any need for the outermost BEGIN...END block; SQL Server doesn't require a BEGIN...END block after the AS clause. Robert Bradley

http://www.foxdev.com/

Got extra money lying around? Visit:

http://www.agrainofhope.org