The Art Of The SQL Server Stored Procedure: Cursors And Loops

I Don’t See Nothin’ Wrong

---

You will, for better or worse, run into occasions in your database career that necessitate the use of loops and cursors.

While I do spend a goodly amount of time reworking this sort of code to not use loops and cursors, there are plenty of reasonable uses for them.

In most cases, I would prefer to see developers write a loop manually, because there are many difficult to understand cursor options that change their behavior, and can really mess with query performance.

• My Favorite SQL Server Cursor Options: Local Static
• Why does FAST_FORWARD make some cursors so much faster in SQL Server?

Those are just a couple examples from my site, without getting into all the many other variations in cursor types.

One very important distinction in cursor types is what set of data the queries in them will see.

As an example, static cursors make a copy of the results of the select, and then use that copy (like a temporary table) to work off of. Dynamic cursors read the underlying data on each iteration.

Especially in cases where your cursors may modify data, and you expect changes to the data to be reflected in future iterations of the cursor, static cursors are likely not a good choice.

Likewise, if you’re already putting data into a temporary table for the cursor to work off of, there is likely no benefit to a static cursor type because you’re just making another copy of the temporary table’s contents.

As I’ve said before, it’s up to you to test different options to see which works correctly and performs the best for your needs.

Also as I’ve said before, never trust the defaults. When you just write DECLARE CURSOR with no options specified, SQL Server may make a lot of bad decisions.

Appropriate Uses

---

In general, I’d expect many readers to understand when a loop or cursor is the only (or best) way to accomplish what you need.

I use them in several of my stored procedures because they’re the only sensible way to get things done.

Notably, in sp_LogHunter:

DECLARE
    c
CURSOR
    LOCAL /*Nothing outside of this needs to reference it*/
    SCROLL /*I want the FETCH FIRST command available to me*/
    DYNAMIC /*I don't want to make another copy of my temp table*/
    READ_ONLY /*Okay, perhaps this is over communicating*/
FOR
SELECT
    command
FROM #search;

The only real additional point I’d make here is that much like with dynamic SQL, if you’re going to use these options, you’re doing yourself (and anyone else who many need to run or manage your code) a great service by building verbose debugging options and error handling into it.

Knowing things like:

• Starting row counts
• Iteration count/progress
• Current parameter/variable values
• Rows in play per iteration
• Iteration timing

Are all quite useful landmarks in your running code, help you find logical errors, and which parts you may be getting stuck on from a performance point of view.

Also from sp_LogHunter:

IF @debug = 1
BEGIN
    RAISERROR('log %i of %i', 0, 1, @l_log, @h_log) WITH NOWAIT; /*Which log file I'm working on, out of how many I need to process*/
    RAISERROR('search %i of %i', 0, 1, @l_count, @t_searches) WITH NOWAIT; /*Which search I'm working on, out of how many I need to run*/
    RAISERROR('@c: %s', 0, 1, @c) WITH NOWAIT; /*The current search command syntax*/
END;

These are all things that helped me while writing it for reasons listed above.

You, like me, may be surprised where your loops take you when they finally sally forth.

Thanks for reading!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.