@@FETCH_STATUS
-
I have a stored procedure that has two cursors running one after another. I say while @@fetch_status = 0 to enter the loop. when the first one finishes fetch_status is -1 and wont enter the 2nd cursor. can i manually set fetch_status? Thanks in advance Stephen
-
I have a stored procedure that has two cursors running one after another. I say while @@fetch_status = 0 to enter the loop. when the first one finishes fetch_status is -1 and wont enter the 2nd cursor. can i manually set fetch_status? Thanks in advance Stephen
Let's see the code. It sounds like you haven't "primed the pump" so to speak. The way to do it is to setup your cursor, read the first record, THEN enter the loop controled by @@FETCH_STATUS. Something like this:
DECLARE curMasterGames CURSOR FOR SELECT MasterGameID FROM MasterGamesOPEN curMasterGames
DECLARE @GameID bigint
-- Prime the pump.
FETCH NEXT FROM curMasterGames INTO @GameID
-- Check to see if the FETCH failed on the first record BEFORE we try
-- to do any processing on that records data.
WHILE @@FETCH_STATUS = 0
BEGIN
-- It seems the FETCH worked, do something with it.
EXEC UpdatePlayerScoresOnMasterGameID @GameID
-- Get the next record.
FETCH NEXT FROM curMasterGames INTO @GameID
END
CLOSE curMasterGames
DEALLOCATE curMasterGames
-- Do the same exact pattern for the next cursor you open.
-- Prime, test for failure, then process your loop.RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome -- modified at 10:37 Friday 20th January, 2006
-
Let's see the code. It sounds like you haven't "primed the pump" so to speak. The way to do it is to setup your cursor, read the first record, THEN enter the loop controled by @@FETCH_STATUS. Something like this:
DECLARE curMasterGames CURSOR FOR SELECT MasterGameID FROM MasterGamesOPEN curMasterGames
DECLARE @GameID bigint
-- Prime the pump.
FETCH NEXT FROM curMasterGames INTO @GameID
-- Check to see if the FETCH failed on the first record BEFORE we try
-- to do any processing on that records data.
WHILE @@FETCH_STATUS = 0
BEGIN
-- It seems the FETCH worked, do something with it.
EXEC UpdatePlayerScoresOnMasterGameID @GameID
-- Get the next record.
FETCH NEXT FROM curMasterGames INTO @GameID
END
CLOSE curMasterGames
DEALLOCATE curMasterGames
-- Do the same exact pattern for the next cursor you open.
-- Prime, test for failure, then process your loop.RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome -- modified at 10:37 Friday 20th January, 2006
Nice one, thats exactly the problem, thank you ver much
-
Let's see the code. It sounds like you haven't "primed the pump" so to speak. The way to do it is to setup your cursor, read the first record, THEN enter the loop controled by @@FETCH_STATUS. Something like this:
DECLARE curMasterGames CURSOR FOR SELECT MasterGameID FROM MasterGamesOPEN curMasterGames
DECLARE @GameID bigint
-- Prime the pump.
FETCH NEXT FROM curMasterGames INTO @GameID
-- Check to see if the FETCH failed on the first record BEFORE we try
-- to do any processing on that records data.
WHILE @@FETCH_STATUS = 0
BEGIN
-- It seems the FETCH worked, do something with it.
EXEC UpdatePlayerScoresOnMasterGameID @GameID
-- Get the next record.
FETCH NEXT FROM curMasterGames INTO @GameID
END
CLOSE curMasterGames
DEALLOCATE curMasterGames
-- Do the same exact pattern for the next cursor you open.
-- Prime, test for failure, then process your loop.RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome -- modified at 10:37 Friday 20th January, 2006
Hi Dave. I love your responses to CP questions. Always thorough and informative. Just thought I'd offer you a virtual :beer: for your efforts.
-
Hi Dave. I love your responses to CP questions. Always thorough and informative. Just thought I'd offer you a virtual :beer: for your efforts.
Hey, Thanks! I'm glad to hear to it! But, I'm not always this way! :-D I can get rushed by my job and just don't have the time to put in a full answer (maybe I shouldn't answer then :-D), but I try. I also have lots of room for improvement. I can get pretty short with some people. Specially when they ask a question like "what's wrong with the object I'm holding behind my back?" and don't give ANY details about the code or error message. I swear, I'm just waiting for the post that says "My app doesn't work." and that's it, nothing else! :laugh: But, if the question is a good one and all the necessary tidbits are there to figure out what they're trying to do with the code, I'll take the time to dig up the details of the answer and come up with an example if I can. RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
-
Hey, Thanks! I'm glad to hear to it! But, I'm not always this way! :-D I can get rushed by my job and just don't have the time to put in a full answer (maybe I shouldn't answer then :-D), but I try. I also have lots of room for improvement. I can get pretty short with some people. Specially when they ask a question like "what's wrong with the object I'm holding behind my back?" and don't give ANY details about the code or error message. I swear, I'm just waiting for the post that says "My app doesn't work." and that's it, nothing else! :laugh: But, if the question is a good one and all the necessary tidbits are there to figure out what they're trying to do with the code, I'll take the time to dig up the details of the answer and come up with an example if I can. RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
I know the feeling - all around :)