I know I've brought this up before on some forum, but this pattern keeps emerging and giving me grief.
Basically, I'm building a utility that updates records, typically customer records. It does some complex thing that I spend a lot of time proofing and optimizing. And when I'm done, I want to be able to run the utility in one of two ways: either I want to update a specific customer record, or I want to update them all. I have always done this the same way, and I cannot come up with another, basically I have a line that says:
and ((@customer_no is null) or (c.customer_no = @customer_no))
This isn't a problem for the case of doing them all, but for doing a single record, the difference between that and c.customer_no = @customer_no is on the order of 20x slower, which is a big deal for the single customer run where you are likely to have significant performance considerations (i.e. stuffing it into LP_CUSTOMER_RANK).Is there a better pattern for this? And heaven help you if you wanted to be able to optionally add lists...
and c.customer_no = @customer_no
The end result is that I am having to build separate utilities with 99% of the code copied back and forth.
Along the lines of what John A. Moskal II is doing, could you have a local table that just holds spid and customer #, with a unique key constraint containing both columns? Then you could populate the table for the current run (like John is doing with his temporary table) and reference that table filtered by spid for the rest of the proc. At the end, you could delete the rows from the local table for the current spid.
I'm not sure if that's any better then John's approach, but it may be worth a try.