CODEWITHSUNDEEP
mysqlsql-server
cwyers
cwyers

Reputation: 35

MS SQL equivalent to MySQL user defined variables in queries

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?

Upvotes: 3

Views: 468

Answers (3)

Marc Shapiro
Marc Shapiro

Reputation: 571

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
;

Upvotes: 3

Niels Keurentjes
Niels Keurentjes

Reputation: 41968

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.

Upvotes: 3

plalx
plalx

Reputation: 43728

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;

Upvotes: 3

Related Questions