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.



Advertisements

No comments yet

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: