jschillm
asked on
Problem with SQL Server Cursors
Hi,
I am nesting 2 cursors,
First Cursor retrieves ID's from 1 table,
Second(Inner) Cursor retrieves IDs from temp table
Within the Inner Cursor I do some evaluations and
Insert to a table.
Then when done, loop the outer cursor to the next ID and do the inner cursor stuff again.
I am experiencing problems with both Cursors at different times. Sometimes the Outer Cursor does not seem to finish looping through, leaving out the last ID, sometimes the Inner Cursor doesnt seem to finish. I can't for the life of me figure this out.
Weirdest thing is, when I step through this in the debugger in both Interdev and SQL Server Management Console, It runs perfectly. I only have 170 points so thats all I can offer, but if anyone has a sec that can peak at the following code and give me any ideas, I would greatly appreciate it. I have been working this problem from so many agles for 3 days now and can't get it to work right.
Is there any specific things to watch out for when nesting cursors? Could the @@FETCH_STATUS from the inner Cursor be screwing with the outer Cursor?
Thanks,
DECLARE RECUR_CURSOR CURSOR FOR
Select ID from Recurrence where eventID = @eventID
Open RECUR_CURSOR
FETCH NEXT FROM RECUR_CURSOR INTO @recurID
WHILE @@FETCH_STATUS = 0
Begin
Declare CalUser_Cursor Cursor FOR
Select CalID from #CalUserID order by CalID asc
Open CalUser_Cursor
Fetch Next From CalUser_Cursor into @CalUID
While @@Fetch_Status = 0
Begin
Select @calCnt = count(ID) from TestTable
where RecurID = recurID and CalID = @CalUID
IF @calCnt < 1
Begin
Insert into TestTable(RecurID, CalID)
Values(@recurID, @CalUID)
End
Fetch Next From CalUser_Cursor into @CalUID
End
Close CalUser_Cursor
FETCH NEXT FROM RECUR_CURSOR INTO @recurID
END
I am nesting 2 cursors,
First Cursor retrieves ID's from 1 table,
Second(Inner) Cursor retrieves IDs from temp table
Within the Inner Cursor I do some evaluations and
Insert to a table.
Then when done, loop the outer cursor to the next ID and do the inner cursor stuff again.
I am experiencing problems with both Cursors at different times. Sometimes the Outer Cursor does not seem to finish looping through, leaving out the last ID, sometimes the Inner Cursor doesnt seem to finish. I can't for the life of me figure this out.
Weirdest thing is, when I step through this in the debugger in both Interdev and SQL Server Management Console, It runs perfectly. I only have 170 points so thats all I can offer, but if anyone has a sec that can peak at the following code and give me any ideas, I would greatly appreciate it. I have been working this problem from so many agles for 3 days now and can't get it to work right.
Is there any specific things to watch out for when nesting cursors? Could the @@FETCH_STATUS from the inner Cursor be screwing with the outer Cursor?
Thanks,
DECLARE RECUR_CURSOR CURSOR FOR
Select ID from Recurrence where eventID = @eventID
Open RECUR_CURSOR
FETCH NEXT FROM RECUR_CURSOR INTO @recurID
WHILE @@FETCH_STATUS = 0
Begin
Declare CalUser_Cursor Cursor FOR
Select CalID from #CalUserID order by CalID asc
Open CalUser_Cursor
Fetch Next From CalUser_Cursor into @CalUID
While @@Fetch_Status = 0
Begin
Select @calCnt = count(ID) from TestTable
where RecurID = recurID and CalID = @CalUID
IF @calCnt < 1
Begin
Insert into TestTable(RecurID, CalID)
Values(@recurID, @CalUID)
End
Fetch Next From CalUser_Cursor into @CalUID
End
Close CalUser_Cursor
FETCH NEXT FROM RECUR_CURSOR INTO @recurID
END
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Okay, since I don't know if what I gave you will work or not, I went ahead and wrote up another approach. If the other one doesn't work, try this one instead.
SELECT
R.ID As RecID,
C.CalID
FROM
Recurrence R,
#CalUserID C
WHERE
CHECKSUM(R.ID, C.CalID) NOT IN (SELECT CHECKSUM(RecurID, CalID) FROM TestTable)
ORDER BY
CalID ASC
But that will only work on SQL 2000. Is that an issue?
Dex*
SELECT
R.ID As RecID,
C.CalID
FROM
Recurrence R,
#CalUserID C
WHERE
CHECKSUM(R.ID, C.CalID) NOT IN (SELECT CHECKSUM(RecurID, CalID) FROM TestTable)
ORDER BY
CalID ASC
But that will only work on SQL 2000. Is that an issue?
Dex*
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
jshillm,
Before, I forgot to filter on @eventID:
SELECT
R.ID As RecID,
C.CalID
FROM
Recurrence R,
#CalUserID C
WHERE
R.ID NOT IN (SELECT RecurID FROM TestTable WHERE CalID = C.CalID)
AND
R.EventID = @eventID
ORDER BY
CalID ASC
You should use the query analyzer to try all of these suggestions. Some of them may be faster that the others.
Dex*
Before, I forgot to filter on @eventID:
SELECT
R.ID As RecID,
C.CalID
FROM
Recurrence R,
#CalUserID C
WHERE
R.ID NOT IN (SELECT RecurID FROM TestTable WHERE CalID = C.CalID)
AND
R.EventID = @eventID
ORDER BY
CalID ASC
You should use the query analyzer to try all of these suggestions. Some of them may be faster that the others.
Dex*
ASKER
Hi Guys,
Thanks so much for your quick feed back, both answers pointed me in the right direction and helped out tons.
When I tested Dex's solution, it almost did what I wanted, it brought up all of the records that needed to be inserted, if none had been inserted for a given CALID, however if there were 5 RecurIDs and there was 1 RecurID associated with the CalID, it did not show the 4 recurIDs that still needed to be inserted. It did help me though and give me pointers,
I used Xenon Joe's ansswer with a little modification, and it gave me exactly what I was looking for. One thing though, I did get an error when having the 'Order By' clause in a subquery, so i took it out.
I would have like to have split the points, but I did not get the option when I accepted the answer, any way of doing this after the fact?
PS, Is it possible to Delete from a table using similar code above?
Thanks so much for your quick feed back, both answers pointed me in the right direction and helped out tons.
When I tested Dex's solution, it almost did what I wanted, it brought up all of the records that needed to be inserted, if none had been inserted for a given CALID, however if there were 5 RecurIDs and there was 1 RecurID associated with the CalID, it did not show the 4 recurIDs that still needed to be inserted. It did help me though and give me pointers,
I used Xenon Joe's ansswer with a little modification, and it gave me exactly what I was looking for. One thing though, I did get an error when having the 'Order By' clause in a subquery, so i took it out.
I would have like to have split the points, but I did not get the option when I accepted the answer, any way of doing this after the fact?
PS, Is it possible to Delete from a table using similar code above?
I think you can split the points after the fact if you ask the moderators to help you.
Post a comment in Community Support and tell them what you'd like to do, and include a link to this question so they know which one to deal with.
Community Support
https://www.experts-exchange.com/Community_Support/
This Question
https://www.experts-exchange.com/questions/20775204/Problem-with-SQL-Server-Cursors.html
Glad you got it working,
Dex*
Post a comment in Community Support and tell them what you'd like to do, and include a link to this question so they know which one to deal with.
Community Support
https://www.experts-exchange.com/Community_Support/
This Question
https://www.experts-exchange.com/questions/20775204/Problem-with-SQL-Server-Cursors.html
Glad you got it working,
Dex*
yap....you can also delete using a code similar to the above... if you have problems post it here and I will help you solve it...
good luck,
xenon
PS: thx :)
good luck,
xenon
PS: thx :)
Try this query:
SELECT
R.ID As RecID,
C.CalID
FROM
Recurrence R,
#CalUserID C
WHERE
R.ID NOT IN (SELECT RecurID FROM TestTable WHERE CalID = C.CalID)
ORDER BY
CalID ASC
It *SHOULD* return all the rows that need to be inserted into TestTable. But its the end of the day, and I could've messed up the query. If it works, then you can modify it to this, and you'll be done without any cursors at all.
SELECT
R.ID As RecID,
C.CalID
INTO
TestTable
FROM
Recurrence R,
#CalUserID C
WHERE
R.ID NOT IN (SELECT RecurID FROM TestTable WHERE CalID = C.CalID)
ORDER BY
CalID ASC
Hope this helps,
Dex*