Tuesday, August 14, 2012

Strange SQL Query Behavior

I faced a strange issue today:

Consider this query in a Stored Procedure:

Select a,b,c from Table1 where (a=@a) or (@a is null)

It is supposed to return all the rows in Table1 when @a is null right? Well, it didn't. I went crazy for a moment there and realized the variable name "@a" is same as the column name, "a" (in this case), so I thought it is getting messed up in the optimizer. 

I renamed the variable @a as @vc_a, and it solved the problem.

So, here is my new quote (well, sort of new..) SQL Server works in mysterious ways.

No comments: