TSQL LAB 2 - Writing Conditional WHERE Clauses

Introduction

Most of the times we need to
apply a conditional WHERE clause in the queries/stored procedures that we write
for reporting purposes. We need conditional WHERE because, we should apply the
filter only if the user has specified a value in one or more filters.

There are different methods that people use to write
queries that need conditional WHERE clauses. One approach that many people do is
to write a dynamic query string and execute it. But there are other people who
do not agree with creating dynamic queries. One of the reasons for not using
dynamic queries is that, with dynamic queries we will not benefit from the
cached execution plan. The intention of this post is not to favor any of
those approaches. Instead, this post tries to look into various methods that we
could use for writing conditional WHERE clauses.

This post does not present anything new. It just tries to review the approaches
that most of us use often and summarize them for a quick reference. There are times when
a dynamic query will give better performance than a static sql statement. Again, there are times where a
dynamic sql statement is not prefereable. In this session we will look at a few different approaches.

Creating and Executing a Dynamic Query

Let us look at a very simple stored procedure which creates a dynamic query based on the parameter values and executes it.

Note:
All the examples presented in this session uses the AdventureWorks sample database of SQL server 2005

/*

EXECUTE ProductSearch1 NULL

EXECUTE ProductSearch1 'AR'

*/

CREATE PROCEDURE ProductSearch1

(

@ProductNumber VARCHAR(20)

)

AS

SET NOCOUNT ON

DECLARE @sql VARCHAR(MAX)

SET @sql = ' SELECT * FROM Production.Product '

IF @ProductNumber IS NOT NULL BEGIN

SET @sql = @sql + ' WHERE ProductNumber LIKE ''' +

@ProductNumber + '%'''

END

-- use the print statement for debugging

-- PRINT @sql

EXEC(@sql)

One of the issues with this apparoach is the compile-time error checking. One needs to be very careful when modifying
this type of queries. Some of the modifications might result in incorrect SQL query getting generated, which either had syntax errors or incorrect column names.

The query will become little more tricky when you have more than one parameters.
The tricky part is about adding the WHERE clause. Here is a new version of the query.

/*

EXECUTE ProductSearch2 NULL, NULL, NULL

EXECUTE ProductSearch2 'CA', NULL, NULL

EXECUTE ProductSearch2 NULL, 'Black', NULL

EXECUTE ProductSearch2 NULL, NULL, 375

EXECUTE ProductSearch2 'CA', 'Black', NULL

EXECUTE ProductSearch2 'CA', 'Black', 375

*/

CREATE PROCEDURE ProductSearch2

(

@ProductNumber VARCHAR(20),

@Color VARCHAR(10),

@ReOrderPoint INT

)

AS

SET NOCOUNT ON

DECLARE @sql VARCHAR(MAX)

SET @sql = ' SELECT * FROM Production.Product '

-- We should add the WHERE clause if at least one parameter

-- is non-null

IF @ProductNumber IS NOT NULL

OR @Color IS NOT NULL

OR @ReOrderPoint IS NOT NULL

BEGIN

SET @sql = @sql + ' WHERE '

END

IF @ProductNumber IS NOT NULL BEGIN

SET @sql = @sql + ' ProductNumber LIKE '''

+ @ProductNumber + '%'''

END

-- Decision for adding an 'AND' operator

-- is also tricky

IF @Color IS NOT NULL BEGIN

IF @ProductNumber IS NOT NULL

SET @sql = @sql + ' AND '

SET @sql = @sql + ' Color LIKE '''

+ @Color + '%'''

END

IF @ReOrderPoint IS NOT NULL BEGIN

IF @ProductNumber IS NOT NULL OR @Color IS NOT NULL

SET @sql = @sql + ' AND '

SET @sql = @sql + ' ReorderPoint = ' +

CAST(@ReOrderPoint AS VARCHAR)

END

-- use the print statement for debugging

--PRINT @sql

EXEC(@sql)

Have a closer look at the code. The code has grown complex. There are a few tricky conditions. First of all, the decision to include a WHERE clause or not. The next problem is the decision to use an 'AND' operator or not. There will be cases when we forget to add a space in front of the operators. For example, some times we might write SET @sql = @sql + 'AND '
instead of SET @sql = @sql + ' AND '.It is always a
good practice to leave a leading and trailing space along with all
operators.

If we have more parameters, the code like the one given above, can grow too complex. I have seen an alternate way of writing such queries. Here is another version of the same query.

/*

EXECUTE ProductSearch3 NULL, NULL, NULL

EXECUTE ProductSearch3 'CA', NULL, NULL

EXECUTE ProductSearch3 NULL, 'Black', NULL

EXECUTE ProductSearch3 NULL, NULL, 375

EXECUTE ProductSearch3 'CA', 'Black', NULL

EXECUTE ProductSearch3 'CA', 'Black', 375

*/

CREATE PROCEDURE ProductSearch3

(

@ProductNumber VARCHAR(20),

@Color VARCHAR(10),

@ReOrderPoint INT

)

AS

SET NOCOUNT ON

DECLARE @sql VARCHAR(MAX)

DECLARE @where VARCHAR(MAX)

-- build the WHERE clause

SET @where = ''

IF @ProductNumber IS NOT NULL BEGIN

SET @where = ' ProductNumber LIKE '''

+ @ProductNumber + '%'''

END

IF @Color IS NOT NULL BEGIN

SET @where = @where +

CASE WHEN LEN(@where) > 0 THEN ' AND ' ELSE ' ' END +

' Color LIKE ''' + @Color + '%'''

END

IF @ReOrderPoint IS NOT NULL BEGIN

SET @where = @where +

CASE WHEN LEN(@where) > 0 THEN ' AND ' ELSE ' ' END +

' ReorderPoint = ' + CAST(@ReOrderPoint AS VARCHAR)

END

SET @sql = ' SELECT * FROM Production.Product '

IF LEN(@where) > 0 BEGIN

SET @sql = @sql + ' WHERE ' + @where

END

-- use the print statement for debugging

--PRINT @sql

EXEC(@sql)

This code looks a little more neat. The real benefit is when you have more parameters to check. What we need is just an IF condition for each parameter that we have. At the end, we construct the final query and execute it.

The 'WHERE' clause should still be added conditionally. We need a WHERE
clause only if the variable @where is not empty. There are 2 things to take care of.

Before adding an "AND" to the filter, check if the string is empty. Add "AND" only if the string is not empty

Add a "WHERE" clause only if the filter string is not empty

I had a friend who used a different version of the above query. He uses "WHERE 1=1" to get out of points #1 and #2 mentioned above. Here is his version of the code.

/*

EXECUTE ProductSearch4 NULL, NULL, NULL

EXECUTE ProductSearch4 'CA', NULL, NULL

EXECUTE ProductSearch4 NULL, 'Black', NULL

EXECUTE ProductSearch4 NULL, NULL, 375

EXECUTE ProductSearch4 'CA', 'Black', NULL

EXECUTE ProductSearch4 'CA', 'Black', 375

*/

CREATE PROCEDURE ProductSearch4

(

@ProductNumber VARCHAR(20),

@Color VARCHAR(10),

@ReOrderPoint INT

)

AS

SET NOCOUNT ON

DECLARE @sql VARCHAR(MAX)

DECLARE @where VARCHAR(MAX)

-- build the WHERE clause

SET @where = ''

IF @ProductNumber IS NOT NULL BEGIN

-- Just go ahead by adding an "AND"

SET @where = ' AND ProductNumber LIKE '''

+ @ProductNumber + '%'''

END

IF @Color IS NOT NULL BEGIN

-- Just go ahead by adding an "AND"

SET @where = @where +' AND Color LIKE '''

+ @Color + '%'''

END

IF @ReOrderPoint IS NOT NULL BEGIN

-- Just go ahead by adding an "AND"

SET @where = @where + ' AND ReorderPoint = '

+ CAST(@ReOrderPoint AS VARCHAR)

END

SET @sql = '

SELECT *

FROM Production.Product

WHERE 1=1 ' + @where

-- use the print statement for debugging

-- PRINT @sql

EXEC(@sql)

There are a few interesting things to look at, here. First of all, he adds "AND"
without checking the length of the filter variable. Secondly, the "WHERE"
clause is always applied. No need to check the length of the string before adding "AND" or "WHERE".

Well, all those methods will come into picture only if you like executing dynamic queries. If you are more in favor of getting things done without dynamic queries, here are a few methods that I have seen in the past.

SQL Injections

The approaches mentioned above have a problem. They are very much prone to SQL Injection. So what is SQL Injection? It is a way of causing damage to your database or running malicious/unwanted queries against your database by passing parameters containing dangerous execution statements. Let us look at an example.

Let us see what happens when we execute the stored procedure that we created above.

EXECUTE ProductSearch4
'CA', 'Black', 375

When the above code is executed, the following SQL is generated:

SELECT *

FROM Production.Product

WHERE 1=1AND ProductNumber
LIKE'CA%'

AND Color LIKE'Black%'

AND ReorderPoint = 375

Well, no issues so far. Now look at what happens if the same query is executed with
the following parameters:

EXECUTE ProductSearch4
'C'';DELETE Product --',NULL,
NULL

When this parameter is passed, the stored procedure will try to execute the following
code.

SELECT *

FROM Production.Product

WHERE 1=1AND ProductNumber
LIKE'C';DELETE
Product --%'

When you run this query, it will delete records from the products table.
This is a basic example of SQL Injection.

Using sp_executesql

By using sp_executesql to execute your dynamc query (instead
of EXEC() ) you can safeguard your queries against almost every possible
SQL Injection. Another advantage of using sp_executesql is the
execution plan reuse. The previous version of the stored procedure can be re-written
using sp_executesql as follows:

sp_executesql is the prefered method over EXEC in most cases. Then again, there are times when EXEC can be handy. You could
see a few such cases in the reference links I have added at the bottom of this article.

Applying conditional WHERE clause without using a Dynamic Query

There are different methods that you could try based on the comparison operator needed. The following example shows how to apply a LIKE operator with WHERE clause.

/*

EXECUTE ProductSearch5 NULL, NULL

EXECUTE ProductSearch5 'CA%', NULL

EXECUTE ProductSearch5 NULL, 'Black%'

EXECUTE ProductSearch5 'CA%', 'Black%'

*/

CREATE PROCEDURE ProductSearch5

(

@ProductNumber VARCHAR(20),

@Color VARCHAR(10)

)

AS

SET NOCOUNT ON

SELECT *

FROM Production.Product

WHERE ProductNumber LIKE ISNULL(@ProductNumber, '%')

AND Color LIKE ISNULL(@Color, '%')

Pretty simple, isn't it? The query is much simpler than what we have seen previously. ISNULL() function works well with LIKE operator. If the parameter is empty, then wild-card character '%' is used for matching. If the parameter is not empty, then the actual parameter is used for the matching.

I have seen people writing this query by using CASE statements. Here is the version which uses CASE statements.

/*

EXECUTE ProductSearch6 NULL, NULL

EXECUTE ProductSearch6 'CA%', NULL

EXECUTE ProductSearch6 NULL, 'Black%'

EXECUTE ProductSearch6 'CA%', 'Black%'

*/

CREATE PROCEDURE ProductSearch6

(

@ProductNumber VARCHAR(20),

@Color VARCHAR(10)

)

AS

SET NOCOUNT ON

SELECT *

FROM Production.Product

WHERE

ProductNumber LIKE CASE

WHEN @ProductNumber IS NULL THEN '%'

ELSE @ProductNumber END

AND Color LIKE CASE

WHEN @Color IS NULL THEN '%'

ELSE @Color END

Another way of writing this is by using OR operator. Here is an example.

/*

EXECUTE ProductSearch7 NULL, NULL

EXECUTE ProductSearch7 'CA%', NULL

EXECUTE ProductSearch7 NULL, 'Black%'

EXECUTE ProductSearch7 'CA%', 'Black%'

*/

CREATE PROCEDURE ProductSearch7

(

@ProductNumber VARCHAR(20),

@Color VARCHAR(10)

)

AS

SET NOCOUNT ON

SELECT *

FROM Production.Product

WHERE

(@ProductNumber IS NULL OR ProductNumber LIKE @ProductNumber)

AND

(@Color IS NULL OR Color LIKE @Color)

CASE and OR will work with most of the comparison operators. However, ISNULL() cannot be used with all operators. It will work with LIKE, =, >= or <= but will not work for conditions like > or <. The following stored procedure shows 3 different ways of writing a conditional WHERE clause for "=" operator.

/*

EXECUTE ProductSearch8 NULL

EXECUTE ProductSearch8 375

*/

CREATE PROCEDURE ProductSearch8

(

@ReorderPoint INT

)

AS

SET NOCOUNT ON

/*

Option 1

*/

SELECT *

FROM Production.Product

WHERE ReorderPoint = ISNULL(@ReorderPoint,ReorderPoint)

/*

Option 2

*/

SELECT *

FROM Production.Product

WHERE ReorderPoint =

CASE

WHEN @ReorderPoint IS NULL THEN ReorderPoint

ELSE @ReorderPoint

END

/*

Option 3

*/

SELECT *

FROM Production.Product

WHERE @ReorderPoint IS NULL OR ReorderPoint = @ReorderPoint

However, if the comparison operator that we need is "<", ">" or "<>", only the 3rd option will work.

/*

EXECUTE ProductSearch9 NULL

EXECUTE ProductSearch9 375

*/

CREATE PROCEDURE ProductSearch9

(

@ReorderPoint INT

)

AS

SET NOCOUNT ON

/*

Option 1

This will not work if "@ReorderPoint" IS NULL

*/

SELECT *

FROM Production.Product

WHERE ReorderPoint > ISNULL(@ReorderPoint,ReorderPoint)

/*

Option 2

This will not work if "@ReorderPoint" IS NULL

*/

SELECT *

FROM Production.Product

WHERE ReorderPoint >

CASE

WHEN @ReorderPoint
IS NULL THEN ReorderPoint

ELSE @ReorderPoint

END

/*

Option 3

Winner!

*/

SELECT *

FROM Production.Product

WHERE @ReorderPoint IS NULL
OR ReorderPoint > @ReorderPoint

As you could see from the comments, it would be difficult to make certain comparison
operators work with CASE and ISNULL. But the option using OR
seems to be helping out in most of the cases.

Conclusions

I have not done a performance comparison of all the different methods. Each of the
methods mentioned above has its own place. There are times when a specific method
is found to be better than other options. I would suggest that you check the different
methods and compare the performance of each method in the context of the specific
query that you are writing. I am expecting that some of the SQL Server experts around
will jump in and share their views and experiences on this subject.

Just after I concluded this short article, I happened to see two great articles on this subject
by SQL Server MVP Erland Sommarskog. I suggest you should read it if you ever intend to work with
conditional where clauses in your applications.

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands
of articles and SQL scripts, a library of free eBooks, a weekly database news roundup,
a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals
that makes it such a success.