How does chaining variable assignments work in SQL?

Question

I'm analyzing some old SQL code in a stored procedure.

Declare @Var1 money, 
    @Var2 money, 
    @Var3 money,

etc...

Select @Var1 = OldValue, 
       @Var2 = @Var1,

etc...

So I'm wondering how these assignments work when they are both in the same select statement. I'm assuming Var2 = OldValue after the select is called, but I want to be sure.

What are the rules surrounding this situation? Are the assignments executed in the order that they are declared? If so, what value would be assigned to Var2 in the following case:

Select @Var2 = @Var1,
       @Var1 = OldValue,

Thanks!

It is currently being used in a somewhat simple stored procedure and seems to be working. So I guess I'm pretty sure that it is functioning as intended. Either way, its just bad style in my opinion. — Brett, Apr 22 '11 at 17:23

score 10 · Accepted Answer · answered Apr 22 '11 at 17:15

DECLARE @Var1 MONEY = 100, @Var2 MONEY = 50 

SELECT @Var1 = @Var2, 
       @Var2 = @Var1 

SELECT  @Var1, @Var2

Returns

--------------------- ---------------------
50.00                 50.00

So at in that case they were executed in Left to Right order but this cannot be relied upon!

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.

Source http://msdn.microsoft.com/en-us/library/ms187953.aspx

Thanks for the good answer, and the link to the documentation really makes it awesome. — Brett, Apr 22 '11 at 17:35

score 1 · Answer 2 · answered Apr 22 '11 at 16:14

In the case of @var2 = @var1 first, I believe var2 is NULL.- they will be value types, so there isn't any referencing going on in the background as you would have in a language like .Net

you can always test by extracting the sql code, the putt the results into a table variable and then select from that to see what you have.

score 1 · Answer 3 · answered Apr 22 '11 at 17:22

1

Why wouldn't you just do this if you want @val2 to have the same value as @val1

  Select @Var2 = OldValue,
         @Var1 = OldValue,

answered Apr 22 '11 at 17:22

HLGEM

94,695
15
113
186

You would do that. Its bad code, and I'm in the process of replacing it entirely. – Brett Apr 22 '11 at 17:33
From the pseudocode specified in the question it isn't clear what "OldValue" means. If "OldValue" is some nondeterministic expression then HLGEM's suggestion may not assign the same value to both variables. – nvogel Apr 27 '11 at 08:40

How does chaining variable assignments work in SQL?

3 Answers3

Linked