How does chaining variable assignments work in SQL

问题:

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!

回答1:

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

回答2:

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.

回答3:

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

  Select @Var2 = OldValue,
         @Var1 = OldValue,