Implementing a Dynamic WHERE Clause

By Garth Wells on 14 January 2001 | Tags: Dynamic SQL

Garth Wells passed on another article to us. This one covers building a dynamic where clause. And the really neat part is that you don't actually need to write dynamic SQL. This is soooo much simpler.

One of my first Internet-related projects was a data-driven web site for a commercial real estate firm. One of the features they wanted the site to support was an interface that allowed potential customers (tenants) to search for properties using one or more search criteria. At the time, the only way I knew how to create a dynamic WHERE clause was by using dynamic SQL. With dynamic SQL, you build the SELECT statement based on the search criteria supplied.

Let's look at an example that shows how this works. Assume you have a table called Customers created with the following statement.

CREATE TABLE Customers
(
 Cus_ID int PRIMARY KEY IDENTITY,
 Cus_Name varchar(30) NOT NULL,
 Cus_City varchar(30) NOT NULL,
 Cus_Country varchar(30) NOT NULL
)

The users want to query the table on the Cus_Name, Cus_City and Cus_Country columns independently or in combination. In other words, they want to be able to specify none, one, or more than one criteria and have the resultset filtered accordingly. Using dynamic SQL, the statements (partial) needed to create the query are shown here.

DECLARE @SQL varchar(1000)

SET @SQL = 'SELECT Cus_Name, Cus_City, Cus_Country FROM Customers '

IF @Cus_Name IS NOT NULL OR @Cus_City IS NOT NULL OR _
    @Cus_Country IS NOT NULL
 SET @SQL = @SQL + 'WHERE '

IF @Cus_Name IS NOT NULL
 SET @SQL = @SQL + 'Cus_Name = ' + @Cus_Name
...
EXEC(@SQL)

The final SELECT is a function of the variables used to store the criteria specified by the users and is executed with the EXEC statement.

The dynamic SQL approach will certainly work, but it has two downsides. The first is that it is cumbersome to implement. Building SQL statements in this manner is an error-prone endeavor that takes a lot of time to get right. The second downside has to do with query performance speed. It is impossible for SQL Server's query processor to re-use execution plans produced by dynamic SQL statements. An execution plan is how the database engine actually retrieves the data from the database. When static SQL is used, execution plans can be re-used by different calls to the same statement. This results in faster query processing time because one less step is required to process the query.

The COALESCE Function

A more efficient approach to creating dynamic WHERE clauses involves using the COALESCE function. This function returns the first non-null expression in its expression list. The following example shows how it works.

DECLARE @Exp1 varchar(30),
        @Exp2 varchar(30)

SET @Exp1 = NULL
SET @Exp2 = 'SQL Server'

SELECT COALESCE(@Exp1,@Exp2)

--Results--

------------------------------
SQL Server

The function processes the expression list from left-to-right and returns the first non-null value. The COALESCE function can process an infinite number of expressions (e.g., COALESCE(@Exp1,@Exp2,@Exp3,@Exp4,...)), but for the example presented in this article only two are needed.

Now that you know how COALESCE works, lets see how it is used to produce an efficient SELECT that supports a multi-parameter search. Each comparison operation in the WHERE clause must be modified to handle NULL values. The following shows the modified statement.

SELECT Cus_Name,
       Cus_City,
       Cus_Country
FROM Customers
WHERE Cus_Name = COALESCE(@Cus_Name,Cus_Name) AND
      Cus_City = COALESCE(@Cus_City,Cus_City) AND
      Cus_Country = COALESCE(@Cus_Country,Cus_Country)

The COALESCE function will return the first non-null value, so when a value is provided for a parameter it is used in the comparison operation. When a value is not supplied for a parameter, the current column value is used. A column value always equals itself, which causes all the rows to be returned for that operation.

Let's add some data to the Customers table with the following INSERTs so we can test the statement.

INSERT Customers VALUES ('Acme','Paris','USA')
INSERT Customers VALUES ('Bouvier','Paris','France')
INSERT Customers VALUES ('Acme','Houston','USA')

The following shows how to return all the rows with Cus_City value equal to 'Paris.'

DECLARE @Cus_Name varchar(30),
                  @Cus_City varchar(30),
                  @Cus_Country varchar(30)

SET @Cus_Name = NULL
SET @Cus_City = 'Paris'
SET @Cus_Country = NULL

SELECT Cus_Name,
               Cus_City,
               Cus_Country
FROM Customers
WHERE Cus_Name = COALESCE(@Cus_Name,Cus_Name) AND
      Cus_City = COALESCE(@Cus_City,Cus_City) AND
      Cus_Country = COALESCE(@Cus_Country,Cus_Country)

--Results--

Cus_Name                       Cus_City        Cus_Country
------------------------------ --------------- ---------------
Acme                           Paris           USA
Bouvier                        Paris           France

Wrapping the Statement in a Stored Procedure

The most efficient way to implement this type of search is with a stored procedure. The statement shown here creates a procedure that accepts the required parameters. When a parameter value is not supplied it is set to NULL.

CREATE PROCEDURE ps_Customers_SELECT_NameCityCountry
@Cus_Name varchar(30) = NULL,
@Cus_City varchar(30) = NULL,
@Cus_Country varchar(30) =NULL
AS
SELECT Cus_Name,
       Cus_City,
       Cus_Country
FROM Customers
WHERE Cus_Name = COALESCE(@Cus_Name,Cus_Name) AND
      Cus_City = COALESCE(@Cus_City,Cus_City) AND
      Cus_Country = COALESCE(@Cus_Country,Cus_Country)

The following shows that you can call the procedure with zero, one or more parameters to retrieve the desired resultset.

--Example 1

EXEC ps_Customers_SELECT_NameCityCountry

--Results--

Cus_Name                       Cus_City        Cus_Country
------------------------------ --------------- ---------------
Acme                           Paris           USA
Bouvier                        Paris           France
Acme                           Houston         USA

--Example 2

EXEC ps_Customers_SELECT_NameCityCountry @Cus_Country = 'USA'

--Results--

Cus_Name                       Cus_City        Cus_Country
------------------------------ --------------- ---------------
Acme                           Paris           USA
Acme                           Houston         USA

--Example 3

EXEC ps_Customers_SELECT_NameCityCountry @Cus_Name = 'Acme',
                                         @Cus_City = 'Houston'
--Results--

Cus_Name                       Cus_City        Cus_Country
------------------------------ --------------- ---------------
Acme                           Houston         USA