Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

COALESCE Vs ISNULL Expand / Collapse
Author
Message
Posted Thursday, November 11, 2010 8:42 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 12, 2010 4:24 AM
Points: 41, Visits: 102
Comments posted to this topic are about the item COALESCE Vs ISNULL
Post #1019676
Posted Thursday, November 11, 2010 8:42 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,160, Visits: 2,191
Nice question, thanks!
Post #1019677
Posted Thursday, November 11, 2010 9:28 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, November 20, 2014 5:13 AM
Points: 1,131, Visits: 1,394
Good one point question. Thanks

Thanks
Post #1019691
Posted Thursday, November 11, 2010 9:34 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, November 20, 2014 5:13 AM
Points: 1,131, Visits: 1,394
As per the BOL:
If all arguments are NULL, COALESCE returns NULL with Note: At least one of the null values must be a typed NULL.

Then why "SELECT COALESCE(NULL,NULL) AS [COALESCE]" is failed?


Thanks
Post #1019695
Posted Friday, November 12, 2010 12:19 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 7:23 AM
Points: 2,668, Visits: 5,924
Because in this case ALL the arguments are "untyped" nulls.

so
declare @a int
select coalesce(@a,null)

will retunr NULL (and not an error).

Mike





Post #1019740
Posted Friday, November 12, 2010 12:55 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 10:49 AM
Points: 1,194, Visits: 791
simple one today
Post #1019749
Posted Friday, November 12, 2010 1:30 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 3, 2014 1:33 AM
Points: 1,065, Visits: 873
An interesting question, but am I missing something here? When would you use coalesce entirely with untyped values?

If I were using coalesce it would be to test column values or variables/parameters and in both these cases typing is specified. Can anyone think of a situation where you would be sending only untyped values?
Post #1019758
Posted Friday, November 12, 2010 2:15 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
I found it interesting that the expression "ISNULL(NULL, NULL)" is of type INT.

SELECT ISNULL(NULL, NULL) AS A
INTO QOTD_TABLE;

EXEC sp_help 'QOTD_TABLE';

-- Column_name Type
-- ------------- ------
-- A int

Is there any reasonable explanation on this behavior?
Post #1019770
Posted Friday, November 12, 2010 2:54 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, December 12, 2014 3:32 AM
Points: 1,639, Visits: 5,721
Interesting point there, vk-kirov. I guess that means that the default type for NULL is int--but in that case, there's not really any such thing as an "untyped NULL" and the COALESCE thing becomes a bit strange!
Post #1019774
Posted Friday, November 12, 2010 2:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:52 PM
Points: 13,636, Visits: 11,509
Nice question.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1019776
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse