MS SQL equivalent to MySQL user defined variables in queries

Question

In MySQL, if you want to report a formula and then use that result in another formula, you can do something akin to:

SELECT @var1 := column1+column2 AS var1
, POWER(@var1,2) AS var2squared
FROM database.table;

Is there an equivalent in MS SQL Server?

Answer 1

As far as I know there's no equivalent in SQL Server.

But you can always use a CTE:

WITH data AS (
    SELECT column1 + column2 AS var1
    FROM table
)
SELECT var1, POWER(var1, 2) AS var2squared
FROM data;

Answer 2

This is not possible in this way because MSSQL actually works correctly. MySQL is essentially a procedural wrapper around SQL, and as such this construct can work because it processes from left to right - as such @var1 exists when the second field is evaluated.

MSSQL correctly executes SQL as an atomic table based querying language, so the selected data as a whole is available when evaluating the fields, and they are then all evaluated based on the intermediate result.

The workaround posed in the other answer is the nice way to solve the issue.

Answer 3

You can also use CROSS APPLY:

SELECT v.Var1, POWER(v.Var1, 2) AS Var2Squared
    FROM [Table] t
    CROSS APPLY (SELECT t.Column1 + t.Column2 AS Var1) v
;