CODEWITHSUNDEEP
sqlsql-server
mmvsbg
mmvsbg

Reputation: 3588

Declare a variable with SELECT SQL statement to be used in the WHERE clause

I have a very long SQL statement which has the following skeleton (using SQL server 2005):

SELECT
    a.something AS Something,
    b.otherthing AS Otherthing,
    (SELECT another FROM ... WHERE...) AS Importantvariable,
....
FROM...
INNER JOIN...
INNER JOIN...
WHERE a.columnname = (SELECT another FROM ... WHERE...) ....

This Importantvariable is a very long query by itself. However, it is used in other parts of the same query, including the WHERE clause and the INNER JOIN clauses. My question is, how can I somehow save it's value so I don't have to write the whole query every time. In this example above I would like to type a.columnname = Importantvariable instead of the whole query. I tried using DECLARE and save the value in a declared variable but it's not allowing me to do so with the following error:

A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operation.

Of course, that makes sense but my whole point is to perform data retrieval, I just don't want to copy and paste the very long query that defined the Importantvariable on several different places in the query. Any ideas?

Upvotes: 2

Views: 2748

Answers (5)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67331

You can use APPLY to create something like a named variable row-wise. If the value is calculated once and is valid for the whole set (more like a constant), you might use a CTE (starting with WITH before the SELECT).

The following will use an APPLY to find the table's name for all columns. You can use The.ImportantVariable in the column list as well as in a WHERE-clause:

SELECT The.ImportantVariable
      ,c.*
FROM sys.columns AS c
OUTER APPLY(SELECT name FROM sys.objects AS o WHERE c.object_id=o.object_id) AS The(ImportantVariable)
WHERE The.ImportantVariable LIKE 'a%';

UPDATE Compare CTE and APPLY

Check this! There are some constant values in 1 single row provided by the CTE which you can CROSS JOIN into your query and use as named constants 

WITH SomeConstants AS
(
    SELECT 'I''m a constant value' AS Constant1 --might be a complex statement too!
           ,0 AS Constant2
)
SELECT The.ImportantVariable
      ,Constant1
      ,Constant2
      ,c.*
FROM SomeConstants
CROSS JOIN sys.columns AS c
OUTER APPLY(SELECT name FROM sys.objects AS o WHERE c.object_id=o.object_id) AS The(ImportantVariable)
WHERE The.ImportantVariable LIKE 'a%'

Upvotes: 3

IngoB
IngoB

Reputation: 2989

I recommend using a CTE like so:

;with cte as
(
SELECT
    a.something AS Something,
    b.otherthing AS Otherthing,
    (SELECT another FROM ... WHERE...) AS Importantvariable,
....
FROM...
INNER JOIN...
INNER JOIN...
)
select *,
       <maybe more uses of Importantvariable>
  from cte
 WHERE columnname = Importantvariable;

Upvotes: 0

Peter
Peter

Reputation: 850

Use an outer or cross apply. It makes your query better readable and probably faster aswell. 

SELECT
    a.something AS Something,
    b.otherthing AS Otherthing,
    Importantvariable,
....
FROM...
INNER JOIN...
INNER JOIN...
outer apply (SELECT another  AS Importantvariable FROM ... WHERE...)
WHERE a.columnname = Importantvariable ....

Upvotes: 0

Matt
Matt

Reputation: 879

It sounds like you are looking for a VIEW. A view is essentially a stored SQL statement that can be called like a table name.

SELECT​ * FROM view_name ... WHERE ...

often a view contains a number of joins and columns with aliases, allowing you to create virtual objects without duplicating data across multiple SQL tables.

Look at your documentation for specifics, but here is a link to W3schools examples https://www.w3schools.com/sql/sql_view.asp

Upvotes: 0

Mike Nakis
Mike Nakis

Reputation: 62129

What you are looking for is Microsoft SQL Server's Common Table Expressions (CTE)

You can read about them here: https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx

What you do is 

WITH (columns)
AS (query)
SELECT (another query using (possibly many times) the previous query)

Upvotes: 0

Related Questions