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.
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:
Post a Comment