Triple Nested Cursor Issue

Question

Hello,

Using SQL Server 2008 R2 to develop, production server is SQL Server 2012.

I am working with a Stored Procedure that contains 3 nested cursors (call them A, B and C).  The procedure (up until now) has worked without issue, however when the data the procedure uses contains only a single row for cursor A, the loops do not work as designed and the results are incorrect.

I apologize for the lack of DDL, however the procedure is quite large and I'm hoping there's something simple here I'm overlooking.  Is there a way to check for/handle the top-level cursor only having a single row, and once the nested cursors in that loop complete, exit?  As a check now, we have:

IF @var = @maxVar
    BREAK

@var is set on the fetch, and @maxVar is the last value to expect.  Which works if there's more than one row for cursor A.  But if there's not (which I've been told is a situation we need to handle as it will happen more and more from now on), we need to be able to handle that accordingly.  Also, I am fully aware of the fact that set based operations are almost always preferable to cursors, however the client won't go for a re-design so I'm stuck patching someone else's work.

Any help is greatly appreciated!

Thanks.

Answer 1

@@rowcount variable will indicate total number of rows cursor result set returned.

Answer 2

>> Using SQL Server 2008 R2 to develop, production server is SQL Server 2012.

This is VERY VERY VERY... VERY bad idea!

These two version are so different that it should never be done!

It remind me all the people that develop application which work great on the laptop but once it go to production it failed. Unfortunately I have seen this a lot :-(

* A developing version cost about 50$ and you should buy it if you want to develop and you do not have the same version as the production.

>> I apologize for the lack of DDL, however the procedure is quite large

Please try to reproduce the issue with a simple case as you can and post DDL+DML. Otherwise we should do this job for you instead of focusing on the real question. after all we do need a table and data in order to execute our testing. In the mean time this can be only theoretical discussion.

>> Is there a way to check for/handle the top-level cursor only having a single row, and once the nested cursors in that loop complete, exit?

Yes there are several options depanding on how your ST nested. It could help to get the structure of the SP at least:

1. The current nesting level is returned by the @@NESTLEVEL function. You can use this probably in order to check each level of nested separately.

2. You can add IF in the command that call the nested SP and use temporarily the logic of "if 1=2" or you can just make it as a comment. again I have no idea how you call the nested ST or what you are doing.

3. You can filter the rows using the filter option of the query that you are using (add where...)

There are lot of other options... but I feel like I am wasting my time in writing options that might not fit your basic case. Please post DDL+DML for a simple cases as you can, which represent your real case.

---

Ronen Ariely  [Personal Site]    [Blog]    [Facebook]    [Linkedin]

Answer 3

 Also, I am fully aware of the fact that set based operations are almost always preferable to cursors, however the client won't go for a re-design so I'm stuck patching someone else's work.

Talk to them. This is awful. My rule of thumb has been that you write no more than five cursors in your entire career; I may change that no more than three with the current features. 70-80% of what we used to do with cursors is now done with DRI actions today. 

Without DDL or DML, we cannot even give you a kludge. 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 4

You didn't mention where or how @var and @maxVar are used (and @maxVar is set):). If @var is assigned a value by a fetch from A's SELECT, I can only imagine B always being called, even when A merely returns one row. So at least I am left with guessing what the problem concerns:).

As a guess, there are various ways to fetch, but I 'prefer' this way:

declare @curs cursor, @name sysname
set @curs = cursor for SELECT name FROM sys.databases
open @curs
while 1=1
begin
    fetch next from @curs into @name
    if @@fetch_status <> 0 begin break end
    print @name
end

Notice how one (and only one) fetch is performed (as close as possible to the SET) as the first statement within the WHILE 1=1's block.

But Joe Celko is right. No matter what, you will be redesigning their stored procedure, which means you have options (even when a customer is tight fisted :). I realize the current schema might appear to require a cursor, but a  WHILE EXISTS SELECT should be a 'valid' alternative. Or if the following model fits the need, you should consider Joe's https://en.wikipedia.org/wiki/Nested_set_model.

---

Bill