One piece of SQL lore I take for granted is COALESCE(). The dictionary definition describes SQL's use pretty close as “To bring or come together into a united whole”. Books Online states in laymans terms that SQL allows you to pass any number of arguments to the function, and it will return the first non-null one back to you. In other words, it “brings together” many variables, and outputs just one.
For example, if a user wants to search for a record in our Customers table, they may want to search for Firstname, Lastname, Email, Phone, and Address. How do you write your SQL for this?
if len(@Address) > 0
SELECT * from Customers
WHERE address like @Address
if len(@Phone) > 0
SELECT * from Customers
WHERE phone like @Phone
--you get the picture...
Now, what if our users want to search on more than one at a time? Like Firstname and Lastname? Now we have to build our SQL statement manually:
if len(@firstname) > 0
set @where = @where + " AND firstname LIKE '" + @firstname+ "'"
if len(@lastname) > 0
set @where = @where + " AND lastname LIKE '" + @lastname+ "'"
This is time consuming and doesn't allow SQL server to optimize your queries. And it just isn't fun :).
Now check this out. Using our trusty COALESCE, we can get it all in one SELECT:
SELECT * FROM Customers
ISNULL(firstname,'') = COALESCE(@firstname,firstname, '')
AND isnull(lastname,'') = COALESCE(@lastname,lastname, '')
AND isnull(phone,'') = COALESCE(@phone,phone, '')
AND isnull(email,'') like COALESCE(@email,email, '')
AND isnull(address,'') like COALESCE(@address,address, '')
Ok, some further explanation on what's going on here. Let's pretend we only want to search on firstname, so @firstname equals “Scott”. So ISNULL(firstname,'') will equal the firstname of each row we are searching. If the row's firstname happens to be null, it will be replaced with ''. So if there were only one row in our Customers table and the customer's name was “Bob”, ISNULL(“Bob”,'') would evaluate to “Bob”.
Now COALESCE will give us the first non-null value we gave it, so it is very important to look at the order of arguments. COALESCE(@firstname,firstname, '') will return the value of @firstname if it is not null, else it will return the value of the firstname of the row if that isn't null, then last but not least it will return ''. In this case, it will return “Scott”, since we specified it earlier. If we hadn't, it would of returned 'Bob' for that one row.
So here is the kicker. Since we specified “Scott” as our @firstname, the statement evaluates as “Bob” = “Scott”, which is not true and hence will not be returned. But if we hadn't specified “Scott” and left @firstname null, then the expression would be “Bob” = “Bob” which will return true, and the row will be returned. The last case we would have to worry about is if the firstname in that row was null. Looking at our expression, if the firstname is null, ISNULL(firstname, '') would be '', and COALESCE(@firstname,firstname, '') would return either “Scott” if we were searching on it, or '' if not. So either '' = “Scott” which is false, or '' = '' which is true. Pretty Nifty!
“Bob” = “Bob” may seem weird until you look at it more simply. If you write “If 2 = 2” The expression will always return true. Same is true for SELECT statements. “SELECT * FROM Customers WHERE 2 = 2” will always return all records. And as you know, a record is returned if the where clause evaluates to true. So “x = x” will return all records.
So, to look at the whole picture, if we only set @firstname = “Scott”, all the other lines will be ignored since both sides of the equation are always the same. The last couple lines I use “like” to demonstrate that this can work for that also.
I must admit that when I first was working with COALESCE, I had to stare at the screen for a bit to get it straight in my head. But once you get it, you won't forget, and your code will shrink dramatically.