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