Scalar Expressions

Question 0

ISNULL

Answer 0

Replaces nulls with specified replacement value.

Syntax:
USE AdventureWorks2012;
GO
SELECT ISNULL(Weight, 50) as Weight
FROM Production.Product;
GO

Note: ISNULL compared to IS NULL. When searching for NULL values in WHERE have to use IS NULL

Question 1

CASE

Answer 1

Allowed in: 
SELECT
WHERE
HAVING
ORDER BY
CHECK constraints

Use a CASE expression whenever you need to translate one set of defined values into another.

CASE is scalar that returns a value based on conditional logic. It’s an expression and not a statement.

2 types: simple & searched.
Simple is more equality comparisons. It has a single test value/expression immediately after CASE keyword.
Searched CASE allows predicates/logical expressions in the WHEN clauses.

Simple case syntax:
SELECT fld1, fld2, fld3 ..
 CASE fld
 WHEN 1 THEN 'its this'
 WHEN 2 THEN 'its this'
 etc
 etc
 ELSE say something
END AS categoryname

Searched case syntax:
SELECT fld1, fld2, fld3, Val,  ..
 CASE Val
 WHEN Val is something THEN do something
 WHEN Val between x and y THEN do something
 etc
 ELSE say something
END AS categoryname