Published on

July 20, 2010

Writing Conditional WHERE Clauses in SQL Server

When writing queries or stored procedures for reporting purposes, it is often necessary to apply a conditional WHERE clause. This allows us to apply filters only if the user has specified a value in one or more filters. There are different methods for writing queries with conditional WHERE clauses, and in this article, we will explore some of these methods.

One common approach is to write a dynamic query string and execute it. However, this method has some drawbacks, such as the lack of compile-time error checking and the inability to benefit from cached execution plans. In this article, we will review various methods that can be used to write conditional WHERE clauses without relying on dynamic queries.

Let’s start with a simple example of a stored procedure that creates a dynamic query based on parameter values and executes it:

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

EXEC (@sql)

This approach works, but it can become complex and error-prone when dealing with multiple parameters. The decision to include a WHERE clause and the decision to use an ‘AND’ operator can be tricky. To simplify the code, we can use an alternate method:

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'

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

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

EXEC (@sql)

This method uses separate IF conditions for each parameter, making the code more readable and easier to maintain. However, it can still become complex when dealing with more parameters.

An alternative approach is to build the WHERE clause separately and then construct the final query:

CREATE PROCEDURE ProductSearch3
(
    @ProductNumber VARCHAR(20),
    @Color VARCHAR(10),
    @ReOrderPoint INT
)
AS
SET NOCOUNT ON

DECLARE @sql VARCHAR(MAX)
DECLARE @where VARCHAR(MAX)

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

EXEC (@sql)

This method simplifies the code by building the WHERE clause separately and conditionally adding it to the final query. It is especially useful when dealing with multiple parameters.

Another approach, suggested by a friend, is to use “WHERE 1=1” to simplify the code:

CREATE PROCEDURE ProductSearch4
(
    @ProductNumber VARCHAR(20),
    @Color VARCHAR(10),
    @ReOrderPoint INT
)
AS
SET NOCOUNT ON

DECLARE @sql VARCHAR(MAX)
DECLARE @where VARCHAR(MAX)

SET @where = ''

IF @ProductNumber IS NOT NULL
BEGIN
    SET @where = @where + ' AND ProductNumber LIKE ''' + @ProductNumber + '%'''
END

IF @Color IS NOT NULL
BEGIN
    SET @where = @where + ' AND Color LIKE ''' + @Color + '%'''
END

IF @ReOrderPoint IS NOT NULL
BEGIN
    SET @where = @where + ' AND ReorderPoint = ' + CAST(@ReOrderPoint AS VARCHAR)
END

SET @sql = 'SELECT * FROM Production.Product WHERE 1=1' + @where

EXEC (@sql)

This method simplifies the code further by always applying the “WHERE” clause and adding “AND” without checking the length of the filter variable.

It is important to note that the methods mentioned above are prone to SQL Injection, which is a way of causing damage to a database by passing parameters containing dangerous execution statements. To safeguard against SQL Injection, it is recommended to use the sp_executesql stored procedure instead of EXEC. This also allows for execution plan reuse:

CREATE PROCEDURE ProductSearch5
(
    @ProductNumber VARCHAR(20),
    @Color VARCHAR(10),
    @ReOrderPoint INT
)
AS
SET NOCOUNT ON

DECLARE @sql NVARCHAR(MAX)
DECLARE @params NVARCHAR(MAX)

SET @sql = 'SELECT * FROM Production.Product WHERE 1=1'
SET @params = '@ProductNumber VARCHAR(20), @Color VARCHAR(10), @ReOrderPoint INT'

IF @ProductNumber IS NOT NULL
    SET @sql = @sql + ' AND ProductNumber LIKE @ProductNumber'

IF @Color IS NOT NULL
    SET @sql = @sql + ' AND Color LIKE @Color'

IF @ReOrderPoint IS NOT NULL
    SET @sql = @sql + ' AND ReorderPoint = @ReOrderPoint'

EXEC sp_executesql @sql, @params, @ProductNumber, @Color, @ReorderPoint

Using sp_executesql is the preferred method in most cases, as it provides protection against SQL Injection and allows for execution plan reuse.

There are also alternative methods for applying conditional WHERE clauses without using dynamic queries. For example, when using the LIKE operator, the ISNULL function can be used:

CREATE PROCEDURE ProductSearch6
(
    @ProductNumber VARCHAR(20),
    @Color VARCHAR(10)
)
AS
SET NOCOUNT ON

SELECT * FROM Production.Product
WHERE ProductNumber LIKE ISNULL(@ProductNumber, '%')
AND Color LIKE ISNULL(@Color, '%')

This method simplifies the code by using the ISNULL function to handle empty parameters and apply the wildcard character ‘%’ for matching.

Another method is to use CASE statements:

CREATE PROCEDURE ProductSearch7
(
    @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

This method achieves the same result as the previous method but uses CASE statements instead of ISNULL.

For comparison operators other than LIKE, such as “=”, “>=”, or “<=”, the same methods can be used. However, for operators like “>” or “<>”, the OR operator is the preferred method:

CREATE PROCEDURE ProductSearch8
(
    @ReorderPoint INT
)
AS
SET NOCOUNT ON

SELECT * FROM Production.Product
WHERE ReorderPoint = ISNULL(@ReorderPoint, ReorderPoint)

These methods simplify the code and make it more readable, but it is important to choose the appropriate method based on the specific query and performance requirements.

In conclusion, there are various methods for writing conditional WHERE clauses in SQL Server. Each method has its own advantages and disadvantages, and it is important to choose the method that best suits the specific requirements of the query. It is also important to consider SQL Injection prevention and execution plan reuse when writing dynamic queries. By understanding these methods and their implications, developers can write more efficient and secure SQL queries.

For more information on this topic, you can refer to the following articles by SQL Server MVP Erland Sommarskog:

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.