CASE WHEN NULL

[CBear]

Hey,

Anyone know the proper construct for SQL Server stored procedure where you want to trap null in a case statement

eg.
Select
Case EMPLOYEE.Name
when NULL then ''
when '' then ''
else EMPLOYEE.Name END as EmployeeName
From
EMPLOYEE


Thanks,

 

[r937]

select coalesce(EMPLOYEE.Name,'') as EmployeeName ...




r937.com | rudy.ca

 

[mrdenny]

select case when Employee.Name is null then 
      ''
   else 
      Employee.Name 
   end as EmployeeName
from Employee

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[travisbrown]

Can't you just use ISNULL(yourcolumn,'') AS YOURFIELD?

 

[r937]

Can't you just use ISNULL(yourcolumn,'')

yeah, you could, but it's pretty much the same as my suggestion to use COALESCE, n'est-ce pas?




r937.com | rudy.ca

 

[nomadicalloy]

My vote is for "r937" reply. I use isnull lot with "case-when"
is sql.


SQL Help Blog

http://www.xpherion.com/blog.asp

 

[travisbrown]

Yeah, it is I was agreeing with your construct as simpler than Case, giving a similar example.

Question, does COALESCE do exactly the same thing as ISNULL?

 

[r937]

coalesce(a,937) is the same as isnull(a,937)

however, the equivalent of coalesce(a,b,c,937) is a little trickier to write with isnulls

r937.com | rudy.ca

 

[vongrunt]

... and uglier as well:

isnull(isnull(isnull(a, b), c), 937)

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)