5/9/2008 2:13:21 PM
There are some situations where you perform a lot of operations on a very
well-defined subset of a table in an SQL module. A typical case is a process-
keyed table. That is a table that is keyed by @@spid or some other process
key. see http://www.sommarskog.se/share_data.html#prockeyed for a longer
discussion. If you have a procedure that operates on such table, the
procedure may have a bunch of:
SELECT/UPDATE/DELETE ... WHERE processkey = @processkey
This is somewhat error-prone. If the procedure has 20 such operations,
you may fail to observe that the condition on processkey is missing in
one of the queries. So it would be a great improvement you could
package this WHERE clause somehow.
Views cannot do the job, since there is a paramerer. Inline table
functions could, but then you still need to repeat that parameter name
through the code.
Therefore a new feature is needed, one where you can declare a
query in the beginning of your module and work on in throughout
The suggested syntax is:
DECLARE @query AS (SELECT-statement)
You can then refer to @query throughout your module. @query would
work as a macro, and would be reevaluated every time, as would any
variables or expressions in the SELECT statement. The SELECT statement
could be about anything you can put in a view. It should of course be
possible to use a CTE in the SELECT-statement.
As for what to call the features, I originally thought of them as "view
variables" in analogy with table variables, but some of my MVP colleagues
found that name misleading. Maybe Module Query Expressions?
I attach two procedures as an example. originalcode.sp is the original
code for a procedure that I wrote recenly. viewvariable.sp is a
rewritten version where I have employed this suggested feature.
The reduction in number of lines of code is modest below 10%. But
the "view variable" ensures that I emply the conditions on xmlreceiveid
and status throughout.
Obviously, this suggestion is for the release after SQL 2008.