Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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: Tuesday, June 30, 2015 9:47 AM
Points: 2,160, Visits: 2,204
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, October 8, 2015 11:32 PM
Points: 1,132, Visits: 1,395
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, October 8, 2015 11:32 PM
Points: 1,132, Visits: 1,395
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: Sunday, June 19, 2016 12:20 AM
Points: 2,670, Visits: 5,951
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: Thursday, December 31, 2015 5:26 AM
Points: 1,212, Visits: 798
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: Today @ 2:46 AM
Points: 1,381, Visits: 944
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: Saturday, June 6, 2015 9:44 PM
Points: 3,448, Visits: 4,408
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: Tuesday, June 21, 2016 2:43 AM
Points: 1,805, Visits: 6,103
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


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 3, 2016 7:09 AM
Points: 15,149, Visits: 13,037
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 SQLKover.

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

Add to briefcase 12»»

Permissions Expand / Collapse