COALESCE vs CASE WHEN in multi-parameter stored procedures

By Peter Bromberg

The COALESCE SQL Server function is very useful in constructing queries that evaluate multiple input parameters and can often be much faster than CASE .. WHEN .. END statements. COALESCE returns the first nonnull expression among its arguments, and can accept multiple arguments.

Consider the following stored procedure to select rows from the Northwind Employees table:


CREATE PROC [dbo].[FindEmployees]

@LastName varchar(20),
@FirstName varchar(20),
@City varchar(15)

SELECT * FROM dbo.Employees
WHERE 1 = 1
and LastName  like coalesce(ltrim(rtrim(@LastName)),'') + '%'
and FirstName  like coalesce(ltrim(rtrim(@FirstName)),'') + '%'
and City  like coalesce(ltrim(rtrim(@City)),'') + '%'
Order by LastName ASC

You can supply any or all of the parameters, or specify null for any that you do not want. You can even specify the first few letters of a value.

COALESCE vs CASE WHEN in multi-parameter stored procedures  (1047 Views)