April 15, 2010 at 1:32 am
I have declared a cursor inside another cursor. And for all set of outside cursor ID i want to check in the table used in the inside cursor.
But the problem is for first value ID in outside cursor it checks for all rows in inside cursor and for second ID it won't check at all because @@fetch_status has already become -1 for inside cursor showing no more rows to check with second ID and rest of it.
So is there any way to reset @@fetch_status value or how to overcome this error.
i have used @@fetch_status in while loop for both cursors. Is that correct?
Please reply me
April 15, 2010 at 1:49 am
When i have to do a cursor i structure it like this , so this is not a issue
Declare ....
Open
While(0=0) begin
Fetch....
if(@@Fetch_status<>0) begin
break
end
<Code>
end
Close..
Deallocate
April 15, 2010 at 2:03 am
Hey Dave
Thanks a lot for your answer. Your logic may find solution to my problem.
Thank you very much man. I will check with your logic and revert back to u
Sachin
April 15, 2010 at 2:48 am
Hello sachin.. here is a sample code that i use for nested cursors...hope this helps u as well, like Dave's 🙂
Try it out and let us know 🙂
IF OBJECT_ID('TEMPDB..#T1') IS NOT NULL
DROP TABLE #T1
CREATE TABLE #T1 (C1 INT, C2 INT)
INSERT INTO #T1 SELECT 1,1
INSERT INTO #T1 SELECT 1,2
INSERT INTO #T1 SELECT 1,3
INSERT INTO #T1 SELECT 2,1
INSERT INTO #T1 SELECT 2,2
INSERT INTO #T1 SELECT 2,3
INSERT INTO #T1 SELECT 3,1
INSERT INTO #T1 SELECT 3,2
INSERT INTO #T1 SELECT 3,3
INSERT INTO #T1 SELECT 4,1
INSERT INTO #T1 SELECT 4,2
INSERT INTO #T1 SELECT 4,3
SELECT * FROM #T1
SET NOCOUNT ON
DECLARE @COUNTER INT
DECLARE @COUNTER1 INT
DECLARE OUTERCURSOR CURSOR
FOR SELECT DISTINCT C1 FROM #T1
OPEN OUTERCURSOR
FETCH NEXT FROM OUTERCURSOR INTO @COUNTER
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
DECLARE INNERCURSOR CURSOR
FOR SELECT DISTINCT C2 FROM #T1
OPEN INNERCURSOR
FETCH NEXT FROM INNERCURSOR INTO @COUNTER1
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SELECT * FROM #T1 WHERE C1 = @COUNTER AND C2 = @COUNTER1
END
FETCH NEXT FROM INNERCURSOR INTO @COUNTER1
END
CLOSE INNERCURSOR
DEALLOCATE INNERCURSOR
END
FETCH NEXT FROM OUTERCURSOR INTO @COUNTER
END
CLOSE OUTERCURSOR
DEALLOCATE OUTERCURSOR
On a side note, try avoiding cursors mate.. go for some set based code if you could for your issue 🙂
Cheers!
Edit : Added code to close and deallocate cursor
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply