Query Variables

Hi,

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

Thank you all

Re: Query Variables

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/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Query Variables

>My question is if you can define a temporary variable within a query to >store tablesor fields. (Like the LET clause of LINQ).

The answer is of course! Jump in anywhere and you will see what makes
sense:) You can start here:
http://beyondsql.blogspot.com/2007/09/dataphor-all-tables-ar e-typed-variables.html

best,.
www.beyondsql.blogspot.com

Re: Query Variables

steve wrote:

>> My question is if you can define a temporary variable within a query to >store tablesor fields. (Like the LET clause of LINQ).
>
> The answer is of course! Jump in anywhere and you will see what makes
> sense:) You can start here:
> http://beyondsql.blogspot.com/2007/09/dataphor-all-tables-ar e-typed-variables.html
>
> best,.
> www.beyondsql.blogspot.com

Posting only to discuss one's own product is one of the ten early
warning signs of crankery.

Re: Query Variables

On Oct 17, 11:51 am, Ed Murphy wrote:
> steve wrote:
> >> My question is if you can define a temporary variable within a query to >store tablesor fields. (Like the LET clause of LINQ).
>
> > The answer is of course! Jump in anywhere and you will see what makes
> > sense:) You can start here:
> >http://beyondsql.blogspot.com/2007/09/dataphor-all-tables-a re-typed-v...
>
> > best,.
> >www.beyondsql.blogspot.com
>
> Posting only to discuss one's own product is one of the ten early
> warning signs of crankery.

If you had bothered to read the link you'd see it was exactly what the
op was asking for. I guess you can be an sql cop. I'm not so sure
about a detective :)

Re: Query Variables

steve wrote:

> On Oct 17, 11:51 am, Ed Murphy wrote:
>> steve wrote:
>>>> My question is if you can define a temporary variable within a query to >store tablesor fields. (Like the LET clause of LINQ).
>>> The answer is of course! Jump in anywhere and you will see what makes
>>> sense:) You can start here:
>>> http://beyondsql.blogspot.com/2007/09/dataphor-all-tables-ar e-typed-v...
>>> best,.
>>> www.beyondsql.blogspot.com
>> Posting only to discuss one's own product is one of the ten early
>> warning signs of crankery.
>
> If you had bothered to read the link you'd see it was exactly what the
> op was asking for. I guess you can be an sql cop. I'm not so sure
> about a detective :)

Well, this /is/ a SQL group, y'know. The guy is asking whether SQL
has a non-trivial capability, so is quite possibly working on an
existing system that has already undergone a significant chunk of
development in SQL. Responding with "this is easy in "
is all well and good, but if he's going to have to rewrite everything
under the sun to take advantage of it, then it's a bit useless, innit?

You give lots of examples of code written for your system, but what
does your API look like? Typical end-user programs consist of front-end
screens implemented in something like VB or VC# or ASP.NET, calling out
to SQL on the back end with code along the lines of (pseudocode)

loop over exec_sql("select x, y from z order by x, y", x, y)
// do stuff with x and y
end loop

populate_grid(g, exec_sql("select x, y from z order by x, y"))

exec_sql("exec spMyStoredProcedure")

Do you provide something like this for your product, or is the
developer expected to rewrite all front-end screens in the front-end
component of your system? If the latter, then it's really only
useful to people developing new systems, or at least new sub-systems.

In any case, at least you're not as nuts as the guy in this story:
http://forums.worsethanfailure.com/forums/thread/132591.aspx