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: Monday, June 16, 2014 9:38 AM
Points: 2,163, Visits: 2,189
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: Today @ 9:56 PM
Points: 1,013, Visits: 1,287
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: Today @ 9:56 PM
Points: 1,013, Visits: 1,287
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: Yesterday @ 12:14 PM
Points: 2,663, Visits: 5,796
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: Today @ 2:49 AM
Points: 1,179, Visits: 783
simple one today
Post #1019749
Posted Friday, November 12, 2010 1:30 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 10, 2014 3:13 AM
Points: 978, Visits: 844
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: Today @ 9:43 AM
Points: 1,607, Visits: 5,472
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: 2 days ago @ 11:20 PM
Points: 13,252, Visits: 10,133
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