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
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
Comment