Dynamic WHERE Clause with SQL Server Stored Procedure

Working with SQL Server over the years has made me realize just how much learning I have to do before I can classify myself a database guru.  Along the way, I have learned a lot of cool things from various sources, but still have plenty more areas to study.  Most of the things that I’ve learned about SQL Server, as well as many other things, have come from a need for a solution to a particular situation.  What I will be discussing today came from one of those situations.

The problem I had to solve involved querying the database with several different parameter options.  The same data was to be retrieved with each query, but it was the filtering of the data that changed.  My initial reaction was to write a stored procedure for each of the different parameter permutations.  The problems with that approach are numerous, so let’s just say it was too much of a waste of time.  I started looking for alternatives and found this blog post discussing the basics of just what I needed to do.  Since I’m using this blog as a way for me to archive some of the cool things I’ve learned, I’ll put my own spin on the topic here.

The basics are this: make a single stored procedure that will take in all the possible parameters, giving each one a default value of NULL in case it is not needed, and return the necessary data filtered accordingly.  Sound simple?  Actually, it really is.  Here is an example of a stored proc that does just that:

See how simple it is?  Simply give the optional parameters a default NULL value (@CategoryID int = NULL).  Then, and here’s the cool part, make each part of the WHERE clause a conditional that will either evaluate to true if the parameter is null or evaluate according to the requirement.

will return true if the parameter CategoryID has not been given, thus eliminating it from the filtering process.

basically says, "if I have a value, use me."

That’s it!  AND those bad boys together and you’ve got a respectable dynamic query built right into a nice stored procedure!  Isn’t that cool?

5 thoughts on “Dynamic WHERE Clause with SQL Server Stored Procedure”

  1. Hello,

    This is not an ideal solution as I have found out.
    It causes problem when working with large amounts of data.

    The reason is SQL Server may evaluate BOTH sides of the OR clause causing a hit on performance (view your execution plan).

    A much better way would be to use:

    T.CategoryID = ISNULL(@CategoryID,T.CategoryID)

    Ensure good database design that does not allow T.CategoryID to be null and you’re all set.

Comments are closed.