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

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.

 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.

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.

1. Before adding an "AND" to the filter, check if the string is empty. Add "AND" only if the string is not empty
2. 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.

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=1 

AND 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=1 

AND 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:

--EXECUTE ProductSearch5

'CA%', 'Black', 375

CREATE PROCEDURE

ProductSearch5

(

    @ProductNumber VARCHAR(20),

    @Color VARCHAR(10),

    @ReOrderPoint INT

)

AS

SET NOCOUNT

ON

DECLARE @sql NVARCHAR(MAX)

DECLARE @params NVARCHAR(MAX)

-- Build the

basic query

SELECT @sql =

'

    SELECT *

    FROM Production.Product

    WHERE 1=1 '

-- Build the

parameter list

SELECT @params =

'

    @ProductNumber VARCHAR(20),

    @Color VARCHAR(10),

    @ReOrderPoint INT'

IF @ProductNumber

IS NOT NULL

      SELECT @sql = @sql

+ ' AND ProductNumber LIKE @ProductNumber '

IF @Color

IS NOT NULL

      SELECT @sql = @sql

+ ' AND Color LIKE @Color '

IF @ReOrderPoint

IS NOT NULL

      SELECT @sql = @sql

+ ' AND ReorderPoint = @ReOrderPoint '

-- execute the

query

EXEC sp_executesql @sql, @params,

@ProductNumber, @Color, @ReorderPoint

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.

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.

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

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.

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

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.

• http://www.sommarskog.se/dyn-search.html
• http://www.sommarskog.se/dynamic_sql.html

What I could make from the above two articles is that sp_executesql is the winner in most of the cases.