Stored Procedure Filter Tip

Posted February 1, 2005

Ever see a stored procedure that uses its parameters to filter data? You’ve probably seen people use “If” statements to check for nulls, and write a separate query for each. That leads to an exponential number of branches, and can get out of hand quickly.

My solution, which seems to elude most people, is to do the following: 

  Select *  From MyData  Where (@MyParam Is Null Or MyColumn1 = @MyParam )

This method uses a simple “Or” statement to check for null. If the parameter is null, it is not used to filter the results. If you want to add more conditions, it is extremely easy, because you only need to add 1 extra line for each. Just be sure to use parenthesis so that the “And” and “Or” don’t get confusing.

8 Comments so far

  1. waqas on February 7th, 2008

    Nice Tip!

    I will start using this from today in some applications i am building these days.

    Thanks for sharing…

  2. Simon on February 7th, 2008

    I like the style and elegance of your solution which works great for me so far. Looking at alternatives it seems they are trying to use a sledgehammer to crack a nut - great job !

  3. Paal on May 12th, 2008

    NICE !

  4. Kg on September 12th, 2008

    Thank you so much!!! This is fact I was looking for for long time.

    WORK LIKE A CHARM

    Thanks again

  5. Kamil on November 18th, 2008

    I agree with Simon! Simple yet very useful!

    THANKS

  6. Gianluca Maggio cavallaro on December 2nd, 2008

    Hi….
    Another version….
    Select * From MyData Where MyColumn1 = ISNULL(@MyParam,MyColumn1)
    Good Job..
    Gianluca

  7. Durai Prasanna on March 16th, 2009

    Thank You! Excellent Tip!

  8. Jonathan Charles on September 10th, 2009

    This is a brilliant solution - Thanks!

Leave a reply