MS SQL Server / SELECT clause with a CASE expression

In SQL Server, if you have a column which has NULLs and instead of nulls, if you want to display 'Nothing', what would you do?

The following query

SELECT CASE Dept_Name WHEN NULL THEN 'Nothing' ELSE Dept_Name END Dept_Name
FROM Inventory

would still display the nulls and not 'Nothing'.

Workaround:


1>
2> select * from employee
3> GO
ID name salary start_date city region
----------- ---------- ----------- ----------------------- ---------- ------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
3 Celia 24020 1996-12-03 00:00:00.000 Toronto W
4 Linda 40620 1997-11-04 00:00:00.000 New York N
5 David 80026 1998-10-05 00:00:00.000 Vancouver W
6 James 70060 1999-09-06 00:00:00.000 Toronto N
7 Alison 90620 2000-08-07 00:00:00.000 New York W
8 Chris 26020 2001-07-08 00:00:00.000 Vancouver N
9 Mary 60020 2002-06-09 00:00:00.000 Toronto W

(9 rows affected)
1>
2> -- Select with a CASE expression:
3>
4> SELECT ID
5> , Name
6> , CASE ID
7> WHEN 1 THEN 'Mountain'
8> WHEN 2 THEN 'Road'
9> WHEN 3 THEN 'Touring'
10> WHEN Null THEN 'Something'
11> ELSE 'No'
12> END As IDX
13> FROM Employee
14> GO
ID Name IDX
----------- ---------- ---------
1 Jason Mountain
2 Robert Road
3 Celia Touring
4 Linda No
5 David No
6 James No
7 Alison No
8 Chris No
9 Mary No

Better than CASE: There is a specific function for this,
ISNULL ( check_expression , replacement_value )

so your syntax would become

SELECT ISNULL( Dept_Name, 'Nothing' ) Dept_Name
FROM Inventory




Sources:
http://www.java2s.com/Code/SQLServer/Select-Query/CasewhenNULL.htm