Rearranging the joins did it - it's now taking 1 second regardless of what is saved last.

Attaching execution plans would have been overcomplicated. My proc with the final statement...

I shifted the order of the joins and it's now taking 1 second regardless of which was saved last. Thanks for the advice.

Thanks for the reply...it just felt like a hack to convert to smallint, get max and convert back.

Here's what's odd about the derived table - and originally I did a...

Jack Corbett (6/4/2008)

---

It would be easier to answer your question if you posted some DDL, your queries, and the execution plans. It could be as simple as the statistics...

I only used a view this time because the values I needed are needed across 5 different stored procedures, and I'd rather guarantee the various procs are using the same...

Thanks, I like that suggestion.  It looks like I was overthinking myself on my B option.

Is there an advantage to your suggestion over either of mine?  You're taking 2 conditions and turning them into 2 sets of case statements, where each set contains the same...

I'm trying to do this without 2 queries, because that means 2 places to maintain return columns.  While I don't want to ever assume exactly one will always be provided,...