sqlsql-serverstored-proceduresconditional-statements

How to ignore WHERE in a SQL statement

I have a simple statement:

SELECT *  
FROM Articles 
WHERE Category = @category

When the @category is 0 or null, I need the WHERE condition to be ignored and SQL select all articles

Solution

  • The correct solution to this is to generate the query on the client if possible and just not include the clause if the parameter is empty. If LINQ is used, eg with EF Core, it's easy to build a query one part at a time :

    var query=context.Articles.AsQueryable();
if(categoryId>0)
{
    query=query.Where(a=>a.CategoryId=categoryId);
}

    In SQL, there's no way to compare a NULL with any other value, even NULL, so IS NULL needs to be used.

    SELECT *  
FROM Articles 
WHERE (Category = @category OR @category IS NULL or @category=0)
OPTION (RECOMPILE)

    The OPTION (RECOMPILE) part is important. Databases compile queries into execution plans and reuse them instead of recompiling all the time. This saves a lot of time and memory, especially on busy systems. Without OPTION(RECOMPILE) the server will reuse the same execution plan that was generated on the first call. If @category is null, that execution plan will scan the entire table instead of using any indexes that cover Category

    This should NOT be used, because it prevents the use of indexes entirely :

    ISNULL(Category,0)=ISNULL(@category,0)

    Indexes are created from the stored values and can't be used to speed up filtering using function results.