THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

Summer SQL Teaser #2 NULL vs COALESCE

Without running the code, try to guess the output

 

DECLARE @v1 VARCHAR(3)

DECLARE @i1 INT

 

 

SELECT  ISNULL(@i1, 15.00) /2,

      COALESCE(@i1 , 15.00) /2,

      ISNULL(@v1, 'Teaser #2'),

      COALESCE(@v1, 'Teaser #2')

I hope you will use COALESCE instead of ISNULL from now on ;-)

Published Friday, June 08, 2007 5:46 AM by Denis Gobo
Filed under: , ,

Comments

 

Ken Tong said:

ISNULL() returns the same data type of its first parameter, but COALESCE() returns the data type of the parameter with the highest data type precedence. I don't see a problem using ISNULL() here. The point is, how often do you use ISNULL() like this "ISNULL(@int, @decimal)? In most case it is a design problem or typing error. Personally I find ISNULL() least surprising as it always return the same data type as the first parameter.

Though I can't agree but I find this post (and the blog) really useful. Thanks.

June 8, 2007 9:23 PM
 

gladchenko said:

June 9, 2007 2:31 AM
New Comments to this post are disabled

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from http://sqlservercode.blogspot.com/ or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog

Syndication

Privacy Statement