CODEWITHSUNDEEP
t-sqlselectsql-server-2012variable-assignment
gotqn
gotqn

Reputation: 43626

Initializing multiple variables with one SELECT statement

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.

Upvotes: 3

Views: 2762

Answers (2)

deroby
deroby

Reputation: 6002

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) !

Upvotes: 1

qxg
qxg

Reputation: 7036

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

Upvotes: 3

Related Questions