Posted by David Portas on 03/01/06 12:49

Doug wrote:
> Hello,
> I would agree that you do not need cursors in 99 percent of the cases.
> In fact, as an example, I bet for most apps there are at least 300
> selects for every cursor.
>
> But sometimes you just can't get around it. Sometimes the business
> logic just forces you to examine each "record" once, and do weird logic
> on it. And sometimes, the logic is such that one result can change the
> way you process the rest.
>
> I had an app once that took money, and split it into diffferent
> buckets. The calculations were very complex, and the rules were
> actually stored in tables. As an example, sometimes there were
> percentages, sometimes hard amounts, and the number of buckets you
> dumped the money into changed radically. Further, you had to look in a
> table to figure out whether to charge sales tax on the whole thing,
> parts of it, or none of it.
>
> I dumped all this into a stored procedure, and used a cursor to load
> teh rules for splitting the money.
> It was not going to work on set based logic. And, I really didn't want
> the rules and calculations completed on the client level. And if
> something went REALLY wrong, I wanted to back the whole thing out.
> For this, SQL server, stored procedures, and cursors were really cool.
>

Fine example. Exceptions prove the rule.

> It can be "proven" using a mathematic proof that cursors ARE required
> in certain circumstances.

I'm not so sure what you are referring to. Do you have a reference?
Mathematical proofs are usually more concerned with the relational
model rather than with the illegitimate child we call SQL. As regards
RM, given that only relational operations are possible I'm pretty
certain your statement goes against conventional knowledge and proofs
of the completeness of relational languages. Here I'm dimly recalling
the chapters on recursion and completeness from Abiteboul et al
("Database Foundations"). I don't have that book with me at the moment
but I can post a better reference when I get home.

As far as SQL is concerned, SQL99 supports recursive queries and a
procedural language that is Turing-complete so intuitively I'd say you
would be wrong to claim that SQL is incomplete without cursors. Of
course I entirely accept arguments about "practicability" in certainly
DBMSs but that's a long way from a mathematical proof that cursors are
"required".

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

[Back to original message]