Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

TSQL LAB 2 - Writing Conditional WHERE Clauses

By Jacob Sebastian, (first published: 2008/02/27)

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.

  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.

 

/*

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

/*

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.

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

Total article views: 80595 | Views in the last 30 days: 83
 
Related Articles
FORUM

color parameter

How to creat a color parameter

BLOG

Color E-Ink

I love my Kindle, but it uses an older e-ink technology that's a little slow and doesn't support col...

BLOG

Another Color Wheel

Some time ago, I had the opportunity to work on a problem involving the color wheel.  The requiremen...

FORUM

Color in SQL Email

Is it possible to add color when sending email in SQL?

BLOG

Alternating Group Colors and Alternating Row Colors

Alternating Group Colors and Alternating Row Colors After posting my recent blog about Alternating ...

Tags
t-sql    
 
Contribute

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.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones