Archive for November, 2007|Monthly archive page

SQL bug (or a feature) which allows to combine both assignment of value to a variable and retrieval data operation in the same query

I’ve found interesting issue today. Everyone knows “A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.”

So it’s impossible to do something like:

declare @a int

select @a = 1, 1

If you will do so error is raised. But interesting thing is if you will combine assign operation with another assignment in union query you will get no error. Here you have code for an experiment:

declare @a int

select @a = 1

union all

select @a = @a where 0 = 1

With this query you will have 1 displayed J I don’t know what it can be used for, but it’s funny.

And I’ve noticed – minimal variable name which can be declared in SQL Server is no symbol. Following query will work just fine: declare @ int. So you can assign values to @ and do whatever you want.