Post reply

Query

  • December 16, 2021 at 8:37 am

    #3964295

    Hi

    How the where condition works in below code. How IsNull & NullIf works.

    SELECT *

    FROM tblName

    WHERE [ColumnName] = ISNULL(NULLIF(@parameter, ''), [ColumnName])

    Thanks

  • December 16, 2021 at 1:37 pm

    #3964390

    That's an odd construct. NULLIF compares two values. If they're matching, it returns null. Otherwise, it returns the first value, in this case, @parameter. ISNULL checks the first value to see if it's null, and then returns the second value if it is.

    So, what we have here is, if the parameter passed is an empty string (compared to '') it sets the value to NULL. Then, it compares the column to the column, in short, return everything if the string is empty, otherwise, just return the stuff that matches the parameter value. It's a bit of an odd construct. Plus, performance is going to absolutely stink since statistics and indexes can't be used due to the calculations on the values passed. You'll always get scans.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • December 16, 2021 at 2:08 pm

    #3964398

    This follows the same pattern as a "Catch-All" query and Grant is totally correct... both performance and resource usage, even for a small table, is going to suffer a lot compared to doing things the right way.

    Please see the following article for how to do it the right way in what I advertise as being the definitive article on the subject written by Gail "Gila Monster" Shaw.

    https://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

    And notice that Gail's method is NOT subject to SQL Injection.  You have to be careful there.  It's easy to "get lazy" and do it the wrong way.  It's just as easy to do it the right way but you have to pay attention when you're reading the article.  It'll become a "natural" habit in the future after you've practiced it a couple of times.

     

     

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply