Most DBA's will tell you that cursors are bad, or worse. That stems from

developers in the early days of SQL using cursors to accomplish tasks the same

way they did in the programming language of their choice - looping. My standard

line is that you should try to solve the problem in a set based way first and

reserve cursors for these situations:

• Multiple database maintenance type tasks, where you need to run through

  many (or at least more than one) databases

• You just cannot figure out a set based solution. That may sound simple

  and/or lame, but we get paid to solve problems. If you (and your DBA) can't

  figure out a set based solution in 30 minutes, use the cursor. That solves

  the problem and often you gain a deeper understanding of the problem that

  may lead you back to a set based solution.

• You want to leverage logic you've already encapsulated in a stored

  procedure.

I hope you'll agree with the first point, imagine you'll vilify me for the

second point, and maybe scratch your head about the third. The goal today is to

get you to rethink your views on cursors and if enough interest, I may follow up

with a deeper discussion. We'll focus on the third point as a starting place.

First, some background. I believe in using stored procedures for all data

access with the exception of things that intrinsically require dynamic SQL like

searches. I also believe that putting some business logic in a stored procedure

is worth doing if it reduces round trips to the client machine. The limiting

factor to stored procedures is that it's hard to pass in the equivalent of an

array so that you can use that encapsulated logic for one record (request) or

for many.

One example from my background required parsing a string containing order

information (for books, shirts, etc) and splitting it into parent/child tables.

Orders were received via an HTTP post, each post containing all the information

relevant to the order. The post was inserted as a single string into a table so

that it completed quickly, then a job ran frequently to process the new records.

It was an ugly bit of parsing and I fell back on point #2 above, using a cursor

to handle the parsing because there were some special cases I needed to handle.

So, in pseudo code, I had something like this:

create proc usp_ParseOrder @WebOrderID
as
begin trans
some ugly cursor stuff here
commit trans

Possibly I could have solved it set based, but for the sake of discussion

let's say that it performed well enough and had the advantage of being easy to

understand if maintenance was needed that no further effort was warranted. For

the purposes of this discussion it's how I solved the problem inside the

stored procedure but rather that it was complicated to express.

So the job that processed new records looked something like this (again,

pseudo code):

open cursor
for each record in cursor
    exec usp_ParseOrder @WebOrderID
next
close cursor

That's the flaw of building procedures designed to handle a single request

(think of users adding/editing records, etc). They are fast and clean, but if

you want to reuse the logic you either call the procedure repeatedly, refactor

the procedure to handle 1 to unlimited records, or you duplicate the logic for

batch processing.

The flaws are offset by a some nice wins:

• Duration of each transaction should be short compared to a large batch
• Growth of the log file has less chance of getting out of hand. Logging

  100 small transactions provides the chance for the log to roll over where

  doing one big transaction may require the log to grow

• That complex logic is in one place and is pretty easy to work with
• If something goes wrong you roll back one very small transaction

Those potentials wins should also be considerations when you're coding. It's

easy to think batch when you're processing 100 records or even a 1000, but what

about 10k? 100k? Ever roll back a million record transaction?

I think looping to call a stored procedure multiple times to leverage logic

is a valid and useful technique. Be practical and pragmatic about it's

application and you'll do well. I look forward to the discussion!