Blog – Fabiano Neves Amorim

Today I worked in a very interesting T-SQL problem…

Look at the following queries:

DECLARE @i Int, @x Int, @y VarChar(250)

SET @i = 2

SELECT @i = 1,

       @x = @i + 1, — @i(1) + 1

       @y = CASE

WHEN @i = 1 THEN ‘Return 1’

WHEN @i = 2 THEN ‘Return 2’

END

SELECT @x x, @y y

GO

DECLARE @i Int, @x Int, @y VarChar(250)

SET @i = 2

SELECT @i = 1,

       @x = (SELECT @i + 1), — @i(2) + 1

       @y = (SELECT (CASE

WHEN @i = 1 THEN ‘Return 1’

WHEN @i = 2 THEN ‘Return 2’

END))

SELECT @x x, @y y

They are very similar, the difference is that in the second batch I’m using a SubQuery to compute the value of the variables @x and @y.

As you can see, the variable @i is being used in the same SELECT instruction three times, first it receives the value “1”, then the variable @x use the variable @i and the variable @y is also using the @i.

The main point here is, this is not a good practice but, this work fine, let´s look at the results to understand the problem.

Results at SQL Server 2005 SP3, 2008 SP1 and 2008R2:

The second batch returns a different value because the SubQuery was executed first, when the value of @i was 2.

Things start to get confuse when we run it in SQL Server 2000.

Look and guess what is the results in SQL Server 2000 SP4.

Pay attention, It should be included into our migration check list, because it was changed from SQL 2000 to 2005. So, be very carefully in write/analyze these kind of queries.