Nested cursor

Is there a better way to nest cursors in SQL Server (I'm using 2005). Due to the nature of the solution a set-based approach will not work. Essentially I have a list of People. Each Person has Multiple Properties, so at the moment I have something along the lines of ...

Declare PersonCursor
Open PersonCursor
Fetch Next Person Cursor
While FetchStatus <> -1
Declare PropertyCursor (for Select according to current values in PersonCursor)
Open PropertyCursor
Fetch Next PropertyCursor
While @.@.FetchStatus <> -1
Process Each Property
Fetch Next PropertyCursor
End
Close PropertyCursor
Deallocate PropertyCursor
End
Close PersonCursor
DeallocatePersonCursor

I hope it's clear. What I'm asking is (and I don't think it's possible) is that is there a method by which I can Declare the PropertyCursor once, but open it multiple times using different values? I haven't used SQL Server cursors much but have seen this done in other RDBMS's.

Even hints on another angle to approach from would be useful. As I say, the steps involved at the "Process Each Property" stage are not really suited to a set-based approach, as I have to maintain counters, and dynamically build SQL unique to each iteration dependent on the counter values.

Any thoughts much appreciated.

Greg.

If the problem is not suited to a set based approach, why use SQL Server? Surely this would be easier to perform at the client side? You could still have the "process each property" code in a stored procedure if you like, but have it called from the client side instead. Can you use a set based appraoch using a CASE statement? CASE statments allow you to apply quite complex logic using a set based approach as it can do something different for each row returned from the query based on the value of that particular row.

HTH

|||We're using SQL Server mainly because

a) it's a heavily DB intensive process, and

b) in this instance, there is no client - the process is a Start of Financial Year billing process

We're using SQL Server stored procs to generate the billing data, and then SSIS to extract it to be printed.

Greg.

|||

Perhaps if you post more information about your specific calculation we can tell you how to do it using a set-based approach.

Oftentimes, this is actually possible (or we, as implementers, want to make that statement true since cursoring even in one level is often not efficient compared to the equivalent set-based query).

Conor Cunningham

SQL Server Query Optimization Development Lead

|||

A solution is to call in your outer cursor loop a sp where you can handle the property cursor.

|||

You can have the cursors nested, without a stored procedure, but you need to declare/open/fetch/close/deallocate the inner cursor within the WHILE loop of the outer cursor.

Good luck, Dean