I rarely post about SQL Server, which is odd, since that's the one certification test I've bothered to take. And I even passed it!

Anyway, I recently stumbled upon two very nice tips about using SQL Server for doing some things that are typically things that I struggle with. First up: dynamic WHERE clauses. Now, that in itself is not all that exciting, but doing it without?dynamic SQL is pretty exciting. It uses the (in my opinion) unknown COALESCE function.

If you don't know COALESCE, it basically takes a list of values, and returns the first non-null value, so COALESCE(null, null, 'test1', 'test2) will return 'test1'.

So how does help in a where clause? Imagine you have a parameter for CustomerFirstName and CustomerLastName passed into the stored procedure, among others. Here's your coalesce'd where clause:

WHERE CustomerFirstName = COALESCE(@CustomerFirstName, CustomerFirstName)
and CustomerLastName = COALESCE(@CustomerLastName, CustomerLastName)

How does that help? If you pass null for @CustomerFirstName you get the equivalent of CustomerFirstName = CustomerFirstName. It's always true, so you're not querying on first name at all - your query is on last name only, and if that's null, then you're getting all of them - dynamic where clauses!

The next one is also an article from Garth Wells, and it's about dynamic?ORDER BY?clauses. This doesn't use COALESCE. Instead, it's a different use for a CASE statement. Based on a?passed in parameter, you use a CASE statement to decide what to order by.

Example? Ok. Lets say 1 order's by first name and two orders by last name. Here's the code for the ORDER BY:

ORDER BY CASE
?? WHEN @OrderBy = 1 THEN CustomerFirstName
?? WHEN @OrderBy = 2 THEN CustomerLastName
?? END DESC

Nice!

Technorati Tags: SQL