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.