Conditional Where

  • Hi,

    I have a where clause in my query that looks like this:

    WHERE 
    items.UserNumber_02 IS NULL -- ALL ITEMS
    OR items.UserNumber_02 IS NOT NULL -- Table is filtered

    How can I switch between all items and the filtered part with a parameter?

  • Do not do this - it will mess up the query plan.

    If the query is complicated then put it in a view and select from the view with different WHERE clauses.

  • You can't substitute "IS NOT NULL" for a parameter value. Now, if you mean that you want to toss, entirely, IS NOT NULL, then you simply would put:

    ... OR items.UserNumber_02 = @myusernumber ...

    However, that changes the very nature of the query since it will return all the rows that are NULL, plus just the rows that match that value.

    Maybe I'm confused as to what this is supposed to do. It's odd to want everything AND specificity at the same time.

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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • I see now I did not make my question as clear as I should.

    In the WHERE clause, I  want to be able to have the user toggle between

    WHERE items.UserNumber_02 IS NULL        --  ALL ITEMS

    and

    WHERE items.UserNumber_02 IS NOT NULL    --  Table is filtered

    so, I would like to use a parameter.

    Perhaps it is actually really straightforward, but I am not so familiar with using parameters.

  • AH!!!

    Sorry. I really did misunderstand.

    So, Ken is right. Don't do that. That is two, distinct queries. While yes, there are ways to force them into a single procedure or batch, these are poor coding practices. If you really want to do it, I'd suggest the following.

    First, create a wrapper procedure. Something like this:

    CREATE OR ALTER PROCEDURE dbo.ReturnItems
    @ItemID int = NULL
    AS
    IF @ItemID IS NULL
    EXEC dbo.ReturnAllItems;
    ELSE
    EXEC dbo.ReturnOneItem @ItemID = @ItemID;

    Then, create your two distinct stored procedures like this:

    CREATE OR ALTER PROCEDURE dbo.ReturnAllItems
    AS
    SELECT r.RadioName
    FROM dbo.Radio AS r;

    GO

    CREATE OR ALTER PROCEDURE dbo.ReturnOneItem
    @ItemID INT
    AS
    SELECT r.RadioName
    FROM dbo.Radio AS r
    WHERE r.RadioID = @ItemID;

    GO

    Then you can execute the wrapper procedure as follows:

    exec dbo.ReturnItems;
    exec dbo.ReturnItems @ItemID = 30;

    That will keep the logic of two completely different queries apart, while allowing your users to pick what they want. You could also toss the default value of NULL and then require them to pass an @ItemID value as either a specific value, or as NULL.

    This is the best way to do this kind of work. Each query can be tuned independently. Each will get an appropriate execution plan. Anything else involves dynamic queries, which are best left out of most data processing.

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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Thanks again Grant,

    I will see if it works for me...

Viewing 6 posts - 1 through 6 (of 6 total)

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