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:
Hello
Earlier i am bit confused on COALESCE function,Now i am very clear with COALESCE functio.
Nice artilce.
Thanks Guy's
Post a Comment