Cursor within a cursor question

Question

Cursor within a cursor question

Mountain Steve

Ten Centuries

Points: 1125
More actions
July 20, 2012 at 10:20 am

#259679

Just curious to know what is a better practice regarding nested cursors. I based my coding off of the msdn site:
http://msdn.microsoft.com/en-us/library/ms180169.aspx
I was told the following:
Understand, but would prefer you code nested cursors this way to ensure your variables are not getting stepped on.
Again I will follow what my Senior DBA tells me but I want to make sure that I am not adding something to my knowledgebase that is wrong.
here is what I am being asked to do:
declare NameOfFirstCursor cursor for
select <statement>
for read only
open NameOfFirstCursor
fetch NameOfFirstCursor into @parms
select @t_status = @@fetch_status
while (@t_status = 0)
begin
declare NameOfSecondCursor cursor for
select <statement>
for read only
open NameOfSecondCursor
fetch NameOfSecondCursor into @parms
select @t2_status = @@fetch_status
while (@t2_status = 0)
begin
....logic
fetch NameOfSecondCursor into @parms
select @t2_status = @@fetch_status
end
close NameOfSecondCursor
deallocate NameOfSecondCursor
fetch NameOfFirstCursor into @parms
select @t_status = @@fetch_status
end
close NameOfFirstCursor
deallocate NameOfFirstCursor
Here is what I am doing
declare NameOfFirstCursor cursor for
select <statement>
for read only
open NameOfFirstCursor
fetch NameOfFirstCursor into @parms
while @@FETCH_STATUS = 0
begin
declare NameOfSecondCursor cursor for
select <statement>
for read only
open NameOfSecondCursor
fetch NameOfSecondCursor into @parms
while @@FETCH_STATUS = 0
begin
....logic
fetch NameOfSecondCursor into @parms
end
close NameOfSecondCursor
deallocate NameOfSecondCursor
fetch NameOfFirstCursor into @parms
end
close NameOfFirstCursor
deallocate NameOfFirstCursor
always get a backup before you try that.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Mountain Steve Ten Centuries Points: 1125 More actions · Answer 1

Oh and before anyone asks This is a fairly complicated grouping issue I am workin on and even with the nested cursor the query time is single digit miliseconds with tons of extra data as I narrow my rows before ever getting to the cursor.

always get a backup before you try that.

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 2

Is this the part you are asking about?

fetch NameOfFirstCursor into @parms

select @t_status = @@fetch_status <<--- specifically this immediately after the fetch?

while (@t_status = 0)

Actually, not a bad idea. Especially if you find yourself putting code between the fetch and the testing of the status.

Mountain Steve Ten Centuries Points: 1125 More actions · Answer 3

Yes that is it.

I just don't see the benefit in doing it and it adds to code complexity.

always get a backup before you try that.

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 4

Not going to try and convince you it is right or better. I can see why your Senior DBA wants it done that way, I would not have a problem with doing it that way. As I said, it makes sense to capture status of the @@FETCH_STATUS immediately after the fetch, especially if you find yourself adding code between the FETCH and the WHILE loop.

The same is true of capturing the values from such functions as @@ERROR, @@ROWCOUNT, etc.

CapnHector SSCoach Points: 16143 More actions · Answer 5

Mountain Steve (7/20/2012)
Yes that is it.
I just don't see the benefit in doing it and it adds to code complexity.

since you are working with nested cursors it makes sure you are working with the right @@FETCH_STATUS by placing the status in a variable as soon as you execute the fetch. i do this with @@ROWCOUNT allot when i want to run an if on it. just makes sure you don't loose the value until you actually want to reset the value and not when the server in the normal process of running resets the value (the next fetch).

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

Mountain Steve Ten Centuries Points: 1125 More actions · Answer 6

I see what you are saying now.

Makes sense to me. I just didn't understand what the advantage is but it is catching it within the loop so it is like an extra level of redundancy.

always get a backup before you try that.