Initializing multiple variables with one SELECT statement

Question

It is a well known practice to this:

DECLARE @A INT
       ,@B INT

SELECT @A = [Column01]
      ,@B = [Column02]
FROM [dbo].[data]

I am wondering is it true for the following code:

DECLARE @A INT
       ,@B INT

SELECT @A = [Column01]
      ,@B = @A + [Column02]
FROM [dbo].[data]

that @A is always getting value before @B?

In my real case [Column01] and Column02 are an expressions with many columns and T-SQL functions and using @A as reference is simplifying the initialization of @B.

Seems to be an 'almost duplicate' of http://stackoverflow.com/questions/14883930/order-of-execution-in-sql-server-variable-assignment-using-select?rq=1 (which in itself was an almost duplicate of another interesting post btw =) — deroby, Jun 05 '14 at 09:09

score 3 · Accepted Answer · answered Jun 05 '14 at 08:16

3

that @A is always getting value before @B?

The answer is no.

Quoted from Itzik Ben-Gan's Microsoft SQL Server 2012 T-SQL Fundamentals

SQL supports a concept called all-at-once operations, which means that all expressions that appear in the same logical query processing phase are evaluated logically at the same point in time.

Please also check All-at-Once Operations in T-SQL

answered Jun 05 '14 at 08:16

qxg

6,955
1
28
36

Actually, that's apples & oranges. The article describes how an alias cannot be re-used elsewhere. Which I agree with and clearly so does the system as it results in a syntax error. However, when going via variables the situation changes considerably and so far I've not managed to break this. That doesn't mean you're wrong off course; on the contrary, the linked article is absolutely correct in what it says, I'm just not sure it applies to the original question. – deroby Jun 05 '14 at 08:58

deroby · Answer 2 · 2014-06-05T13:12:20.277

In my experience so far this has always worked 'top down' for me. However, I too feel a bit queasy whenever I write something like that and have been known to split it into two separate commands when I had a more paranoid day, maybe I should have more of those =)

That said, the question could be extended to these syntaxes of which I 'assume by experience' they are correct but again wonder if anyone has a more definite answer to it :

DECLARE @a int,
        @b int,
        @x int

SELECT @a = (CASE name WHEN 'A' THEN value ELSE @a END),
       @b = (CASE name WHEN 'B' THEN value ELSE @b END)
  FROM myTable
 WHERE name IN ('A', 'B')

which gives the same result as below but is quite a bit faster, especially if you have to fetch many of them

SELECT @a = value FROM myTable WHERE name = 'A'
SELECT @b = value FROM myTable WHERE name = 'B'

Or, this one:

DECLARE @a int = 8,
        @b int = 5,
        @x int

UPDATE myTable
   SET @x = @a * leftField + @b * rightField,
       mySum = @x,
       mySquare = Power(@x, 2)
 WHERE ...

Where I use @x to calculate an intermediate value for a given record and use said value later on to set a field or as part of a formula again. (I agree it's a stupid example but right now can't come up with something more sensible)

Or this one that now seems to be generally accepted as 'OK', but I do remember the days when this would go haywire if you'd add an ORDER BY to it, which is often a requirement.

DECLARE @a varchar(max)

SELECT @a = (CASE WHEN @a IS NULL THEN name ELSE @a + ',' + name END)
  FROM sys.objects
 WHERE type = 'S'

SELECT @a

UPDATE: these things (likely) usually work fine as long as the datasets are small but odd things start to happen when the size of the data grows and the QO decides to use a different plan using multi-threading etc... So I tried to 'break' things by setting up a largish table that would no longer fit in memory and then see what would happen. I took a simple example that could easily be split into multiple threads. The result can be found here but you'll need to adapt it to your hardware off course (please don't put sqlFiddle to its knees!). The results so far are that things keep working but the query plans differ depending on the query we run (with or without @x and @y) !

Initializing multiple variables with one SELECT statement

2 Answers2