David Portas (REMOVE_BEFORE_REPLYING_dportas@acm.org) writes:
> Everything Erland has said. This is where it pays to have a good design
> pattern from kick-off. For an UPDATE/INSERT/DELETE proc servicing the
> UI you may typically want to pass parameters for a single row. For
> procs that implement other business logic however, you should generally
> design with a set-based approach in mind. Unfortunately, programmers
> used to other languages too often try to encapsulate all logic in procs
> that act like scalar functions - a sure route to cursor hell!

Permit me to expand a bit on what I touched in my previous post.

In many cases it is reasonable to write a procedure that operates
on a scalar set of values. It cannot be denied that writing such a
procedure is simpler, and thus cuts development costs at that stage.

Passing data in tables is actually quite messy. Let's look at the options:
1) Use a temp table. The caller must create the temp table, and the callee
trust the caller. If the procedure is called from many places, many
callers must create the table. This can be address with an include-
file, if you have the luxury of a preprocessor. We have that, but it's
not a standard feature.
And if even you get by all this, the callee is recompiled for each
new instance of the caller. This can be expensive.
2) A permanent table, typically spid-keyed. We use this technique for the
really heavy-duty stuff. If you make this routine, you get lots of
these tables. Note also that the tables are typically stored disjunct
in the version-control system, which means that procedure and
"parameter list" are in two places.
3) Clients can't use any of 1 or 2, but they can pass comma-separated
lists or XML-documents. But if A_SP calls B_SP, it would be a bad
idea if A_SP built an XML document from its data, only to be able
to call B_SP. What you can to is to have a wrapper that accepts
the XML document, and unpacks that into the temp table or spid-
keyed table. If the client is mainly interested in single-row
operations, it probably needs a scalar wrapper as well. Else, it
will be a lot extra development overhead to build XML documents.

So, clearly, if you at point A in your devleopment cycle only have a need
for a procedure that operates on scalar parameters, you write a procedure
that works with scalar procedure only, because that is what you are paid
for.

If you later at point B need to do the same operation on many rows,
you have to make a judicious choice between:

1) Write a cursor loop.
2) Just forget about the old procedure, and write a new set-based.
3) Replace the old procedure.

If the logic of the procedure is trivial, like "IF NOT EXISTS INSERT ELSE
UPDATE" you should pick #2. But say that the logic is non-trivial, for
instance includes updates to dependent tables in some unnormalised
scenario, then at some point #2 becomes completely impermissible. At
this point #1 can very well be the best pick. Say that you know that
it will be rare that the cursor will comprise as much as 100 rows. If
the procedure takes 100 ms to run, it may be very difficult to motivate
to rewrite the old procedure, if this would take 100 hours.

There is also another issue here that is worth mentioning. Say that your
procedure performs some sort of INSERT operation (in a couple of tables),
and the data comes from some less trustable source, which thus may
supply non-conformant data. If you have a scalar procedure, error
handling is fairly simple. You can do explicit checks on anticipated
errors, but you can be fairly relaxed, because if some data violates a
constraint or trigger check, the operation will fail.

This because a lot more complex if you accept input data in a table.
Because if you apply the same strategy, 1000 rows could fail to insert
when there is an error in a single one. This could very likely be
entirely unacceptable. Thus in case, you will need to duplicate all
constraint and trigger checks in your code, so you can mark which rows
that are illegal.

So while it is easy to say "replace cursor loops with set-based
statments", one should realise that in complex cases, this is far from
trivial.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp