Simplify SQL Query using variable

Question

I want to simplfy my T-SQL query. It is like this :

SELECT

    t.a AS [Column A], 
    t.b AS [Column B],
    t.c AS [Column C],
    (t.a - t.b - t.c) AS [Column D],
    CASE
      WHEN (t.a - t.b - t.c) = 0 THEN 'Equals'
      WHEN (t.a - t.b - t.c) > 0 THEN 'Greater'
      WHEN (t.a - t.b - t.c) < 0 THEN 'Less'
    END AS [Status]          

FROM
    Table1 AS t;

It would be nice to put (t.a - t.b - t.c) into a variable, so I can reuse it on all places it occurs, because the expression may change over time. I could not figure out how to do this without changing the existing query significantly.

Answer 1

You can use a CTE (Common Table Expression) like this:

;WITH CTE AS
(
    SELECT
        t.a AS [Column A], 
        t.b AS [Column B],
        t.c AS [Column C],
        (t.a - t.b - t-c) AS [Column D]
    FROM
        Table1 AS t
)
SELECT
    [Column A], 
    [Column B],
    [Column C],
    [Column D],
    CASE
      WHEN [Column D] = 0 THEN 'Equals'
      WHEN [Column D] > 0 THEN 'Greater'
      WHEN [Column D] < 0 THEN 'Less'
    END AS [Status]      
FROM
    CTE

This defines a CTE - something like a "ad-hoc" view - that you can use to handle things like calculations, aggregations etc. and then select from it (or use other SQL statements against it). The CTE only exists for the one, next statement - it doesn't get "persisted" for multiple SQL statements. But it's quite handy to handle situations like this

Answer 2

I don't know what's your question. But here is some information for you.

If you use SQL Server, you can assign the returns of your query to a variable like that:

SELECT @var = value
FROM yourTable

But beware, if you have multiple rows in the result, only the last row will be taken to your variables.

If you just want to save power with your (t.a - t.b - t.c) statement, it won't be necessary as SQL Server will evaluate those expression just one time and make the data compare for each WHEN until one matches.

Answer 3

Use Cross Apply

SELECT
    t.a AS [Column A], 
    t.b AS [Column B],
    t.c AS [Column C],
    [Column D],
    CASE
      WHEN [Column D] = 0 THEN 'Equals'
      WHEN [Column D] > 0 THEN 'Greater'
      WHEN [Column D] < 0 THEN 'Less'
    END AS [Status]          

FROM
    Table1 AS t
    CROSS APPLY (SELECT t.a - t.b - t.c AS [Column D]) AS t2