Posted by Erland Sommarskog on 07/20/05 11:00

Neil (nospam@nospam.net) writes:
> Here's another one for you. I have an sp that takes various input
> parameters for a customer, and processes the data using various case
> statements. I now want to run this sp for all customers on a nightly
> basis. My immediate reaction, as previously, would be to use a cursor to
> loop through all the customers, get the input parameters for the sp from
> the Customer table, and call the sp once for each customer. Is there a
> way to do this without a cursor?

Yes, but you will of course have to rewrite the procedure, so that it
works with many customers. To do this, you need to pass the input
parameters in a table rather than as parameter. This table can be a temp
table, or a permanent table which is keyed by @@spid or similar. I discuss
this on http://www.sommarskog.se/share_data.html#temptables.

Well, rather you would write a new procedure that works with many, and
then rewrite the old procedure to be a wrapper on the new procedure.

Now, whether you actually should go this route depends. Let's say that
it takes 10 minutes to run a cursor over all customers and call the
existing procedure, and that you have plenty of time to spare in the
night. In this case, it's not likely to be worth the development effort.
Also, if you opt to use a temp table to pass the input parameters, the
procedure will be recompiled each time. This will have the net effect
that calls for single customers will now be more expensive, and could
even be performance problems, if the procedure is huge.

We actually did this exercise with a core procedure in our system, and
in our case it was really necessary. But it was a major developement task.
Our estimate was 200 hours for development, but I think the true outcome
was more than 300 hours. But that was a long procedure, on 700-800 lines
and which called several sub-procedures. The final multi-version is a
3000-line monster with no less than 43 table variables.

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

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

[Back to original message]