SQL Filters with Optional Parameters

Lets say, for example, you have some information on Cars that you want to filter buy either make, model, colour, or any combination of these.  I.e. if you select red cars you will get all makes and models that are red, or if you select red, Fords, you will get all models of red Fords…  You get the idea…
 
I used to spend lots of time building dynamic SQL based on whether or not each parameter was set, and then executing the SQL at the end.   If you’re reading this then you already know the drawbacks of dynamic SQL so I’ll skip over that for now.  
 
Then one day I found this ‘magic trick’, not sure how, or where, but I’m glad I did.
 
Lets take the cars example but with only one parameter (the same principle will expand to as many parameters as you have).
 
So, we have the Car table:
 
   CarID
   Make
   Model
   Colour
   CC
   FuelType
 
We want to select Cars with a certain colour, if that parameter is set, or show all if not.  Imagine that the Colour that we are looking for is passed to the @Colour variable (this will be null if no colour is passed).
 
SELECT
   CarID,
   Make,
   Model,
   Colour,
   CC,
   FuelType
FROM Car
WHERE Colour = (ISNULL(@Colour,Colour))
 
So, if @Colour has a value, then only records with this colour will be returned.  If @Colour is null then we will get all the records (as Colour always equals Colour for each record).
 
If you have multiple parameters, as in our initial example, just ‘AND’ them….
 
WHERE Colour = (ISNULL(@Colour,Colour))
   AND Make = (ISNULL(@Make,Make))
   AND Model = (ISNULL(@Model,Model))
 
So… no dynamic SQL
 
 
 
Advertisements
This entry was posted in Programming and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s