4

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.

1 Answer 1

4

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.

Yes, it is. The following is mentioned in the docs on variables:

If there are multiple assignment clauses in a single SELECT statement, SQL Server does not guarantee the order of evaluation of the expressions. Note that effects are only visible if there are references among the assignments.

In short, it's just unsupported behavior with how SQL Server is designed and can lead to unexpected behavior, as you've noticed.

My question is does anyone know if there is a name for this behaviour...

I don't think so officially but I'd just refer to it as "multiple variable assignment in a SELECT statement".

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.