Wednesday, February 23, 2011

Simplifying Complex WHERE Clauses with COALESCE

We have all received these requirements from the user. Someone on the sales team approaches you with this:

“I would like to search customer data by ID, email address, state, country, and by name. Oh, and I’d like the name search to be exact or partial, but I want to be able to choose.”

Immediately, we begin debating the best (and easiest) way to accomplish this request. We could generate a complex SQL statement within our application, so that the WHERE clause includes only the fields we are interested in as criteria. We could create several stored procedures – Get Customer By ID, Get Customers, Search Customers, etc. We could also create just one stored procedure or parameterized query that will accommodate our requirements.

The COALESCE Statement

The procedure we will create will utilize the COALESCE statement in the WHERE clause. This SQL Server function is similar to the ISNULL function, in that you can replace a NULL value with another value. With COALESCE, however, you can provide numerous alternatives:

COALESCE(@ValueA, @ValueB, @ValueC)



Each value is evaluated until the first non-NULL value is found. When used in a WHERE clause, the COALESCE statement allows you to use both local variables and columns. This enables us to create our simplified query.


Using the COALESCE Statement


Here is an example of a single, simple stored procedure that can retrieve a customer’s details based on several optional criteria. (Note – This query uses the AdventureWorks database)


create procedure [dbo].[GetContact] 
@ContactID int
,@FirstName nvarchar(255)
,@LastName nvarchar(255)
,@EmailAddress nvarchar(255)
as
select * from Person.Contact
where ContactID = COALESCE(@ContactID, ContactID)
and FirstName = COALESCE(@FirstName, FirstName)
and LastName = COALESCE(@LastName, LastName)
and EmailAddress = COALESCE(@EmailAddress, EmailAddress)



As you can see, we now have a single procedure that can be used in several ways – retrieve a customer by their primary key, or retrieve a list of customers by first and last name, or email address. This can also be used as a parameterized query in .NET code.


Our next goal is to design the query to support partial name searches. We modify the input parameters to allow the user to specify whether they are using a partial name search. We also take advantage of SQL Server 2005 and 2008 enhancements regarding LIKE operations and string concatenation.


create procedure [dbo].[GetContact] 
@ContactID int
,@FirstName nvarchar(255)
,@LastName nvarchar(255)
,@PartialNameSearch bit
,@EmailAddress nvarchar(255)
as
declare @Wildcard nvarchar(1)

if (@PartialNameSearch = 1)
set @Wildcard = '%'
else
set @Wildcard = ''

select * from Person.Contact
where ContactID = COALESCE(@ContactID, ContactID)
and FirstName like COALESCE(@FirstName, FirstName) + @Wildcard
and LastName like COALESCE(@LastName, LastName) + @Wildcard
and EmailAddress = COALESCE(@EmailAddress, EmailAddress)

What we have now is a dynamic query that supports multiple criteria, and partial or exact name searches. Because no wildcard is specified on an exact name search, the LIKE operator functions as an equals operation. If the caller indicates the partial name search is desired, the first and/or last name will be partially-searched.


Considerations


1) Obviously, using a dynamic query such as this affects your indexing strategy. SQL Server execution plans take into consideration all criteria in the WHERE clause, so index accordingly.


2) To help avoid deadlocks, the criteria in the WHERE clause in all queries should be similarly ordered.


3) This procedure or query requires non-utilized criteria values to be NULL. The calling procedures or applications must be configured to pass NULL (or DbNull from .NET applications) into these non-utilized parameters.