Creating Dynamic Where Clauses with SQL

One very common scenario 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 has its 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.

CREATE TABLE TestData
(
	ColumnA VARCHAR(20),
	ColumnB VARCHAR(25)
)

--Insert test data
INSERT INTO TestData
	(ColumnA)
VALUES
	('Me')

INSERT INTO TestData
	(ColumnA, ColumnB)
VALUES
	('Me', 'You')

INSERT INTO TestData
	(ColumnA, ColumnB)
VALUES
	('You', 'Me')

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

Now that we have the data, we can discuss the implementation 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.

DECLARE @ColumnA VARCHAR(20)
DECLARE @ColumnB VARCHAR(25)
DECLARE @SQL NVARCHAR(1000)

SET @SQL = 'SELECT
	ColumnA,
	ColumnB
FROM TestData
WHERE 1=1'

IF(@ColumnA IS NULL)
BEGIN
	SET @SQL = @SQL + '
               AND ColumnA = ''' + @ColumnA + ''''
END

IF(@ColumnB IS NULL)
BEGIN
	SET @SQL = @SQL + ' 
                AND ColumnB = ''' + @ColumnB + ''''
END

EXEC(@SQL)

Overall this works but there is a risk at the point of concatenating in the @ColumnA and @ColumnB data. A ' or other characters 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.

DECLARE @ColumnA VARCHAR(20)
DECLARE @ColumnB VARCHAR(25)

SELECT
	ColumnA,
	ColumnB
FROM TestData
WHERE ColumnA = COALESCE(@ColumnA, ColumnA)
	AND ColumnB = COALESCE(@ColumnB, ColumnB)

Now, this is a much cleaner option, and the format is fairly 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 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.

DECLARE @ColumnA VARCHAR(20)
DECLARE @ColumnB VARCHAR(25)

SELECT 
	ColumnA,
	ColumnB
FROM TestData
WHERE CASE WHEN @ColumnA IS NULL
			THEN 1
			ELSE ColumnA
	  END = CASE WHEN @ColumnA IS NULL
					THEN 1
					ELSE @ColumnA
	  END
	AND CASE WHEN @ColumnB IS NULL
			THEN 1
			ELSE ColumnB
	  END = CASE WHEN @ColumnB IS NULL
					THEN 1
					ELSE @ColumnB
	  END

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. Feel free to share your feedback below.