Posted by Erland Sommarskog on 10/13/07 22:16

Yota (yotaxp@gmail.com) writes:
> I'm new to SQL Server, but an experienced .Net developer. I'm trying
> to accomplish a query the most efficient way possible. My question is
> if you can define a temporary variable within a query to store tables
> or fields. (Like the LET clause of LINQ) My query makes use of
> subqueries which filter my table (WHEREs, not SELECTs) in the same
> exact way. I'd like to have a subquery at the beginning of my query
> to filter the table(s) once, and then SELECT off it of later in the
> query.
>
> Here is an (utterly poor) example. No, this is not from my project.
> My filter is a little more complex than 'c=@p'.
> ('c' is a column/field, 't' is a table', '@p' is a parameter)
>
> SELECT *
> FROM (SELECT COUNT(c) FROM t WHERE c=@p GROUP BY c)
> CROSS JOIN (SELECT c FROM t WHERE c=@p)
>
> Bottom line, would something like the following be possible?
>
> @v = (SELECT c FROM t WHERE a=@p)
> SELECT *
> FROM (SELECT COUNT(c) FROM @v GROUP BY c)
> CROSS JOIN (SELECT c FROM @v)
>
> I'd like to know if this is possible within a query, but I can move to
> a Stored Procedure if I must. (I'll still need help then.)

Syntactically you can do:

WITH MyCount AS (
SELECT c FROM t WHERE a = @p
)
SELECT *
FROM (SELECT COUNT(c) FROM MyCount GROUP BY c) AS a
CROSS JOIN (SELECT c FROM MyCount) AS b

The WITH clause defines a common table expression (CTE), to which you can
refer for the rest of the query as if it was a table. However, this is
main syntactic sugar: in SQL 2005, SQL Server will always compute the
expression everytime it occur, and never consider to put the result
into a worktable. This could be different in a future version of SQL Server.

If you want to store an intermediate result, you need to use a table
variable or a temp table.

Some more notes on WITH:
1) The statement that precedes WITH must have a ; as statement terminator.
2) WITH is actually not only syntactic sugar: a CTE can refer to itself
under some circumstances, permitting you to wind up recursive
structures.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]