CODEWITHSUNDEEP
sqlsql-servert-sql
Rik Bradley
Rik Bradley

Reputation: 61

Using the result of a 'previous' column in a SELECT statement

T-SQL appears to allow us to use the value of a column in an expression in another column, going left-to-right. E.g:

declare @a int, @b int;
select @a = 2, @b = @a * 3;
select @a, @b;

yields {2, 6}.

I can't find any reference to this 'feature' in BOL etc. My question is, is this known, dependable behaviour?

Upvotes: 6

Views: 2436

Answers (2)

Salman Arshad
Salman Arshad

Reputation: 272396

There is a warning in the docs that reads:

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.

It means there is no guarantee that it will evaluate the expression left-to-right. For this code:

declare @a int, @b int;
select @a = 2, @b = @a * 3;
select @a, @b;

The result could be 2, 6 (@a = ... evaluated first) or 2, NULL (@b = ... evaluated first).

Upvotes: 1

Thom A
Thom A

Reputation: 96015

This is actually covered as a warning in Variables (Transact-SQL):

Warning

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.

(Emphasis mine)

SQL server does assign the values one variable at a time, so in this (simple) example, SQL Server has assigned the value of @a prior to evaluating (and assigning) the expression @a * 3 to @b.

As the warning says though, don't expect the behaviour to always be the same. If you need to guarantee that assignment is in a strict order, do the assignments as separate statements:

DECLARE @a int,
        @b int;
SET @a = 2;
SET @b  = @a * 3;
SELECT @a, @b;

Edit: This behaviour does not exist when using expressions that are not assignments. For example the following statement will fail with the error "Invalid column name 'a'.":

SELECT 2 AS a,
       a * 3 AS b;

The behaviour you are describing only happens for assignment statements. You cannot reference a column by it's alias in the same SELECT it was defined.

Upvotes: 4

Related Questions