Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

Module-level table expressions

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 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 procedure.

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.

1 vote
Sign in
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    AdminMicrosoft SQL Server (Admin, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →
    unplanned  ·  AdminMicrosoft SQL Server (Admin, Microsoft Azure) responded  · 

    Upvotes: 50

    <=-=May 9 2008 7:48PM=-=>

    I browsed the SQL-2003 standard to see if there was something like this, and nothing jumped out. That makes me suspect there’s a good reason. In some respects, this feature could be like Dynamic SQL, only worse.

    Macro-like abilities are very handy, but they can also be tricky to implement and define. Perhaps you would learn to use the feature effectively, but the parser/preprocessor/optimizer would have to handle whatever this feature allowed, and the results might be surprising. For example (and I think there are many examples), suppose you began a long procedure with

    declare @sessionRows query as select, T.c from T where T.spid = @@spid;

    If @sessionRows is expanded before query execution, these two seemingly-identical statements are completely different:

    — Expression A
    with T1 as (
    select spid, id, c
    from Nisse
    where status = ‘Censored’
    update Trades set
    details = ’’
    where exists (
    select * from T1
    join @sessionRows as S
    on =
    where =

    — Expression B
    with T as (
    select spid, id, c
    from Nisse
    where status = ‘Censored’
    update Trades set
    details = ’’
    where exists (
    select * from T
    join @sessionRows as S
    on =
    where =

    So you might say define special name-resolution rules for query variables so that the column specification inside the declaration of @sessionRows would never resolve to the CTE in Expression B. But then what if someone forgot the T. alias here or there in the declaration of @sessionRows. Then you might further decide you needed to require two-part column specifications in query declarations.

    What kind of dependencies would have to be maintained for a procedure that declares and refers to a query variable?

    The list goes on, I’m afraid. I’m not saying it can’t be done, but the implications are much deeper than they seem at first.


    <=-=May 10 2008 2:31AM=-=>

    Just to make it clear, when I say “macro”, I mean “macro” in the same sense
    that a view, inline-table function, CTE is a macro. That is, it’s expanded by the
    algebrizer and the query processor sees very little difference.

    And a more elaborate syntax would be:

    DECLARE @query [(column-list)] AS (SELECT-statement)

    to align with the definition of views and CTEs.

    <=-=May 10 2008 8:54AM=-=>

    I think this functionality would be very useful, but personally I would prefer to see it implemented as a CTE that was scoped to the batch and not to the statement, rather than as a variable.

    <=-=May 12 2008 12:57PM=-=>

    Can someone enumerate the reasons why CTE’s are scoped only a statement level? I have a feeling that may explain why something of this nature (the specific feedback item) has not yet been implemented.

    <=-=May 14 2008 3:27PM=-=>

    Hi Erland,

    Thankyou for this suggestion. I understand the issue. But I’d love to devise a solution that extends existing constructs, rather than invent a new one. So, if CTEs or Table-Valued Functions, for example, don’t quite do what we need, perhaps we could extend their operation to cover.

    The approach of inventing macros seems enticing. But we run into similar binding issues that you highlight with the @sessionRows example: T is not bound via parameter (technically it’s a ‘free’ variable), so it binds to whichever T is in-scope at the callsite; which is error-prone (kinda like old LISP’s dynamic scoping – clamber up-stack at runtime and grab first match!) Macros are powerfull, but again, lack control over their binding (at least, for conventional C-like syntactic macros).

    I’ve added this to our list of candidate features for next release, so it’s on our radar.

    Perhaps we could exchange ideas in email about alternative approaches?

    Thanks again,


    <=-=May 15 2008 1:34PM=-=>

    Again, I’m sorry if my use of “macro” confused you and other people. I used “macro”, beause for me a view, a CTE or an inline-table function is a macro in SQL Server. The algebrizer expands the expression with the definition, and the query optimizer works with the expanded query.

    This suggestion is definitely not intended to break new ground, but is very much intended to align with the current features: view, CTE and inline-function. You could say that my suggestions bridges the gap between the database-scoped views/functions and the statement-scoped CTEs.

    The example with @sessionRows was not mine; that objection was raised by Steve Kass, and I don’t think it’s valid. If you have a “view variabale”, “declared view”, “local view”, “batch-level CTE” or whatever we want to call it, which refers to a table T, and you then have a query that goes:


    @myview would expand to something that refers to the table T, not the CTE T, just like a database-scoped view would do. The example highlights a problem with CTEs: there may be a name clash with an existing object. Notice that this problem does not really exist with my proposal, since I suggest that the module-level CTE would have a leading @ in the name.

    We have had some discussion on the topic in our internal MVP forum, and a lot of it has centered by the name. I tend to favour the name “view variable”, to align with “table variable”. But I certainly do intend the view definition of the view to be variable; you cannot redefine the bacth-scoped view/CTE at run-time. But the result set defined by the view could change as tables or scalar variables referred by the view/CTE are updated.

    If you prefer to continue the discussion over e-mail, drop me a mail. I don’t have your address, so I can’t drop you one. :-)

    <=-=Aug 18 2008 3:20AM=-=>

    The answer has been around for about forty years:) The sql cte is the pale
    attempt at assignment, which is the assignment of a table/expression to
    a ‘variable’ in the same way an integer value is assigned to a one. This is the
    foundation of a ‘relational’ system. But sql cannot realize a peristed table as a
    variable so your limited to a statment. In sql one thinks of a ‘macro’ instead of
    a variable definition since there is no alternative. I wonder how many sql server
    team members have C.J. Date on their bookshelves:) I use Dataphor (in conjunction
    with sql server) to assign a table definition to a variable that can be used anywhere
    and can be modified (this is distinctly different than a view).
    Do you know what the Sql CTE is?
    All tables are typed variables

    To think of all the work put into LINQ could have been for a truly relational (object) system :(:)

    <=-=Mar 24 2009 7:23AM=-=>

    I am very interested if there is a way to have a similar approach like CTE but extend it to a batch or module level construct.

    <=-=Apr 28 2009 4:59AM=-=>

    Instead of enablinf module level CTEs, there is the possibility of allowing the creation of temporary views, just like a temporary table or stored procedures.

    <=-=Sep 20 2013 6:39PM=-=>

    Having Module-level table expressions would be very useful.

    Actually I never could understand why Common Table Expression currently are “one time use only”. So they can be used in one statement only but nowhere else. Can anybody explain why it was designed this way and what benefit it gives? What would be wrong if it was possible to use the same CTE many times within the same stored procedure?


    Sign in
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)

      Feedback and Knowledge Base