Sunday, December 3, 2006

Further uses for CASE Statements

Everyone knows you can use CASE statements in the SELECT clause to specify column output, but it can also be used in other ways >

Computed Columns -
BMI = CASE
WHEN (Height + Weight) IS NULL THEN 'Cannot Calculate'
ELSE Weight / Height
END


Conditional WHERE : Dealing with NULLs on the fly by using CASE statements.

For exact matches, check for columns being equal to themselves i.e. saying TRUE = TRUE
SELECT column1, column2, column3
FROM   schema.table
WHERE  datecolumn BETWEEN @dateRangeFrom AND @dateRangeTo
AND    column4 = CASE WHEN @criteria IS NULL THEN column4 ELSE @criteria END

To use the LIKE clause in the same way, ISNULL can be used -
AND textcolumn LIKE ISNULL(@searchterm,'%')

This replaces an empty search term with the wildcard '%' which of course matches any term.

Conditional ORDER BY -
SELECT column1, column2, column3
FROM   schema.table
ORDER BY
CASE WHEN @orderby = 'column1' THEN column1
WHEN @orderby = 'column2' THEN column2
WHEN @orderby = 'column3' THEN column3
END

No comments: