Don't ask me how its pronounced, but its bloody useful. Looking at the name, I'm guessing that its French. But who knows?
COALESCE ( expression [ ,...n ] )
This function accepts a variable number of expressions, and evaluates them, it returns the left-most non-null value.
Example, COALESCE(null, null, 10, null) would return 10.
Simple yet deadly!
This means you can use it in your where clauses to provide optional paramters.
So what?
If you application is generating dynamic SQL then this function is no big deal. But if you embed your SQL with stored-procedures, this function is dead useful.
CREATE PROCEDURE MyProc @id int, @name varchar(32), @age int
AS
SELECT *
FROM [example]
WHERE [id] = COALESCE(@id, [id])
AND [name] = COALESCE(@name, [name])
AND [age] = COALESCE(@age, [age])
GO
The above procedure can be called passing null for columns that we do not want to filter on. Our friend the COALESCE function will helpfully evaluate each condition to be equivalent to WHERE [field] = [field].
But remember, this function should only be used for good, not evil.
