Also a good point Dave.

What we ended up deciding to do is go the dynamic SQL route. Running it with sp_executesql at least guarantees execution plans and all that stuff (at least I think it does ... from what I've read). Plus I can use parameters for all the other parameters, rather than concatenating the values into the dynamic string.

The string doesn't look too horrendous ... I think it's acceptable.

Thanks everyone for all the suggestions.

~~Bonnie





>Bonnie,
>
>You have to weigh carefully the costs of tripling the I/O (read + write + read vs read) and forcing tempdb hits compared to the overhead of a dynamic query. And if you are the least bit curious open master.sp_rename and look at all the hoops involved in there. *g*
>
>>>I was thinking about splitting your query into 2 steps. A regular query into a temp table and dynamic query from the temp table with calculated columns added.
>>
>>Ah ... good idea Sergey. And while I was reading your reply and thinking about it, my co-worker pointed me to something he had found which is similar, but even better (downside is that it still involves 2 selects):
>>
>>http://www.sommarskog.se/dynamic_sql.html#columnalias
>>
>>Basically it involves selecting into a temp table and then renaming the column(s) in the temp table and then selecting from it.