Re: Query Variables
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Sat, 13 Oct 2007 22:16:45 +0000 (UTC)
Yota (yotaxp@xxxxxxxxx) 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@xxxxxxxxxxxxx
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
.
- References:
- Query Variables
- From: Yota
- Query Variables
- Prev by Date: Query Variables
- Next by Date: Re: Speed Issues After SQL Upgrade
- Previous by thread: Query Variables
- Next by thread: Re: Query Variables
- Index(es):
Relevant Pages
|
