Monday, March 31, 2008

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.

1 comment:

Srinu said...

Hello
Earlier i am bit confused on COALESCE function,Now i am very clear with COALESCE functio.

Nice artilce.
Thanks Guy's