Skip Navigation LinksHome > Categories > Code from a Category

Creating Dynamic Where Clauses with SQL



User Name: codelecturer
Name: Mike Chauhan
Contact Me: www.datawebcoder.com/ContactUs.aspx
Home Page: www.datawebcoder.com
6 years of total IT experience including programming, application development, System Maintenance . 3 years of Experienced and expertise in .Net Framework VB, Visual Basic.Net, ASP.Net 2.0, 3.5 and AD... [More]
Viewed Times: 1182
Add Date: 11/12/2012
In this article we will learn how to create dynamic where clauses with SQL
One very common scenario in when working with User Interface database queries is the concept of a "Dynamic Search Query". A select statement that can have optional items in the where clause. There are a number of methods to implement this, however, they have varying levels of difficulty and, overall each have their own tradeoffs, in implementation, readability, performance, and accuracy. This article explores a few of the most common methods of doing this.

The Setup
Before I start the discussion on the different methods, I want to start with a few scripts that will create the table and data records needed to demonstrate all of the following concepts. The below listing creates a simple table with two VARCHAR columns and fills some data, including a null value.

With this we now have a dataset that looks like this.



Now that we have the data, we can discuss the implemetation of a search procedure that allows the searching of both COlumA and ColumnB, but both searches could be omitted. If nothing is selected, ALL entries should be returned, and not just the "null" ones.

Dynamic SQL
Typically the first idea that comes to someone looking to build a dynamic SQL statement to meet these needs is to build it using Dynamic SQL. Most of us are already aware of the "issues" and risks associated with using dynamic SQL. Typically this should be a "last case" scenario, as doing it is harder and harder to protect yourself. Below is a quick example of how to do it via Dynamic SQL.

Overall this works but there is a risk at the point of concatentating in the @ColumnA and @ColumnB data. A ' or other character could break the script. This is becoming a less supported and recommended method to query the system, and personally a "last ditch" effort.

Write Using Coalesce
The next method, one that I have used in the past is to use the COALESCE function provided in SQL Server to do a comparison. For those unfamiliar with COALESCE, this function takes multiple parameters, and returns the first "non null" item. So if you do something like COALESCE(@ColumnA, ColumnA) if the parameter value was null it would substitute the value from the column, making the condition always true. Below you will find an example of this method.


Now, this is a much cleaner option, and the format is farily easy to read. However, when filtering on data that can have nulls we run into an issue. The result set with our test data is actually similar to the following.

The first entry with a null in ColumnB is not included, this is due to COALESCE not providing a null value to the field and not making the conditional true. So this method, works quite well when dealing with data columns that are NOT NULL, but if nulls are there, it isn't an option.

Using CASE
The final method I will present resolves the issues presented with the COALESCE method, and the security concerns of the dynamic SQL method. WIth anything though, there is a downfall, it is much harder to read, but works fully. This method uses a CASE statement on both sides of the clause, if the parameter is null a value of 1 is used creating a 1=1 clause, if not null it creates a standard format where clause. A sample implementation is below.


This method is VERY hard to read, but it works and doesn't use dynamic SQL to create the query. Looking at the query execution plan though it doesn't seem to have a performance impact when it is compared to the other methods. The best part is that this shows all records, and is truly dynamic and meets our specific needs. Summary I hope that this article has served as a good overview showing a few different methods to create a dynamic where clause in a SQL Server statement. I'm sure there are other ways, to do this, but it is just a few methods that I have found helpful

Happy Programming!!!

.

Post a Comment

Name: (Optional)
Email: (Optional, you can get an email if somebody replys your comments)*
Email me if somebody respons my comment below:
Details**:
Enter Text
as Below:
(case insensitive, if hard to read, click the "get a new one" button)
 
    
* Your email address will not be shared with any third parties for any reason.
** Maximum 1000 charactors.