Post reply

Assigning variables in Where Clause

  • December 12, 2011 at 5:13 pm

    #261385

    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

    #1420317

    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.

    - Craig Farrell

    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

    #1420323

    Thanks Craig

  • December 13, 2011 at 12:55 am

    #1420396

    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