I've encountered some T-SQL code which seems to rely on behaviour I found unexpected.
When assigning values to multiple @variables in one SELECT statement, when one @variable is dependent on another, the order in which the @variables appear in the statement changes the outcome.
For example:-
DECLARE @a INT;
DECLARE @b INT;
SET @a = 0;
SET @b = 0;
SELECT @a = 3,
@b = 5 - @a;
SELECT '@a before @b' AS Test,
@a AS [Value Of @a],
@b AS [Value Of @b];
SET @a = 0;
SET @b = 0;
SELECT @b = 5 - @a,
@a = 3;
SELECT '@b before @a' AS Test,
@a AS [Value Of @a],
@b AS [Value Of @b];
| Test | Value Of @a | Value Of @b |
|---|---|---|
| @a before @b | 3 | 2 |
My expectation was that @b should equal 5 (not 2) - as it does when the assignment order is swapped.
| Test | Value Of @a | Value Of @b |
|---|---|---|
| @b before @a | 3 | 5 |
Microsoft say:- "For assigning variables, we recommend that you use SET @local_variable instead of SELECT @local_variable."
But this doesn't mention why, or whether this behaviour is one of the reasons behind that recommendation.
I can easily convert a single SELECT assignment into multiple SET assignments so it's not a big problem.
This is not the behaviour when updating one table column with a value dependent on another, for example:-
DECLARE @Table TABLE
(
[a] INT,
[b] INT
);
INSERT INTO @Table
(
[a],
[b]
)
VALUES
(0, 0);
UPDATE @Table
SET [a] = 3,
[b] = 5 - [a];
SELECT [a],
[b]
FROM @Table;
| a | b |
|---|---|
| 3 | 5 |
As expected, column [b] is updated based on the value of column [a] before the UPDATE - not the value being assigned to column [a] within the UPDATE.
My question is does anyone know if there is a name for this behaviour so I can refer to it in code reviews (something more eloquent than "dependent variable assignment value depends on assignment order when assigning values to multiple variables in a single SELECT statement")?
The behaviour almost seems reminiscent of a quirky update.
