December 12, 2011 at 5:13 pm
I came across the following query for AdventureWorks Database.
DECLARE @productName VARCHAR(100)
SET @productName='blade'
SELECT * FROM Production.Product p
WHERE
(@productName =p.Name OR @productName IS NULL OR @productName='0')
The output of the Query is as follows:
When @productName ='blade',it will return all rows which has Product Name='Blade'
If @productName is set to NULL or '0',then the query will return all the rows from the Table.
My question is that generally in the Where clause we do assignment as 'WHERE p.Name=@productName'
But in the above query the assignment is 'WHERE @productName=p.Name'
Please can some one explain me how the above mention query work?
Regards
December 12, 2011 at 5:41 pm
jigsm_shah (12/12/2011)
My question is that generally in the Where clause we do assignment as 'WHERE p.Name=@productName'But in the above query the assignment is 'WHERE @productName=p.Name'
There is no difference between the two statements whatsoever. They are logically equivalent, no matter which one you put first. The parser handles it from there.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 12, 2011 at 6:41 pm
Thanks Craig
December 13, 2011 at 12:55 am
Evil Kraig F (12/12/2011)
jigsm_shah (12/12/2011)
My question is that generally in the Where clause we do assignment as 'WHERE p.Name=@productName'But in the above query the assignment is 'WHERE @productName=p.Name'
There is no difference between the two statements whatsoever. They are logically equivalent, no matter which one you put first. The parser handles it from there.
+1
Now jump to best practice (better readability). The pattern 'WHERE p.Name=@productName' is better and mostly followed.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply