Here is an interesting comment from chrisbro about some features he finds sorely missing in TSQL:

* Can't specify a parameter to be used as an IN clause
* Can't easily specify parameters to be used in the ORDER BY clause; especially painful to do multiple order by clauses

I actually hear quite a bit of requests in the same "parameterization" vein, such as "how come I cannot specify a table name as a parameter?". So let's look under the hood for a moment. Although it looks to the user as a "pure interpreter", TSQL is actually a compiler. During the compilation phase, all tables/columns are fetched from the catalogs, the statement gets type checked, and, most importantly, the query processor comes up with a plan for executing the query. Compilation is a pretty expensive process, so we keep the resulting plan in cache and reuse it if we see the same statement again. So, here is a rule: during compilation, we cannot look at the actual variable or parameter values. Otherwise, we would have to compile the query every time it is executed, because the parameter value may be different. So, we restrict parameters to the relatively "benign" positions where they cannot really influence the data types involved in a statement and the execution plan. This allows us to compile a statement once and reuse the plan for different parameter values (of course I am simplifying a bit here, let me know if more details would be of interest). Now, if you look at, say, ORDER BY clause, the execution plan is actually different (in some cases radically different) depending on what columns you sort on. That's why we don't allow you to parameterize this aspect of the query (IN clause is roughly the same deal). Good workarounds for parameterizing the IN clause are described here. When you want to parameterize your ORDER BY clause, you have multiple options, and which one you choose depends on how flexible you want to be on your ordering options and what your performance requirements are. The most performant solution would be to write several separate queries, each one with the particular flavor of ORDER BY that you want, and then choose one of them using IF statement. If you want to be absolutely flexible, you can use dynamic SQL (i.e. compose your statement on-the-fly and execute it using EXEC). This is slower, and requires you to be careful to avoid SQL injection attacks if you take the ORDER BY list directly from the user input (tip: check that this is indeed a simple ORDER BY list before composing the statement).

So, why cannot SQL Server just implement the above "workarounds" as features and allow parameterizing of ORDER BY clause for the user? One reason is performance. Any generic solution would require essentially generating dynamic SQL under the covers. This would incur serious performance overhead in excess of "regular" parameterization, which would be "unexpected" as opposed to the explicit use of dynamic SQL where you are aware that it has perf penalty. Another reason is that we just cannot implement all features in the world, we have to prioritize them. So, keep posting your wishes, and they may come true in upcoming releases.

This posting is provided "AS IS" with no warranties, and confers no rights.