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

Try it out.. Expand / Collapse
Author
Message
Posted Wednesday, August 4, 2010 10:56 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 8:13 PM
Points: 17,628, Visits: 15,486
Thanks for the question.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #963639
Posted Wednesday, August 4, 2010 10:58 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 6:29 PM
Points: 513, Visits: 82
Good Question.
Post #963640
Posted Wednesday, August 4, 2010 10:32 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 30, 2014 2:51 AM
Points: 2,036, Visits: 369
nice info about isnull and coalesce... thanks
Post #963953
Posted Saturday, August 7, 2010 12:48 PM


SSC Eights!

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

Group: General Forum Members
Last Login: Friday, September 12, 2014 5:33 AM
Points: 900, Visits: 1,489
Very nice question.
Fortunately for me, I've come to know the differences of both functions while studying for the MCTS exam.

The best of all are these discussions about the question of the day.
You always get to learn something new and read the opinions of more qualified professionals than me.


Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2
Post #965577
Posted Thursday, September 9, 2010 6:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:05 PM
Points: 157, Visits: 292
Hi,
Thanks for posting this. I have few clarification on COALESCE result type and i am confused the type conversion.

Consider the case below.

--Case 1
DECLARE @STR int
SET @STR=NULL
SELECT COALESCE(@STR,null,CAST('2010-05-05' AS DATETIME))

Result: 2010-05-05 00:00:00.000

--Case 2
DECLARE @STR2 int
SET @STR2=NULL
SELECT COALESCE(@STR2,null,CAST('ABCD' AS varchar(10)))

Result:
Error: Msg 245, Level 16, State 1, Line 9
Conversion failed when converting the varchar value 'ABCD' to data type int.

--Case 3
DECLARE @STR2 int
SET @STR2=NULL
SELECT COALESCE(@STR2,null,CAST('2222' AS varchar(10)))

Result: 2222

In case1, @str is int type, but expression 2 in COALESCE is datetime. But i except the output to be 40301 which is the int value for date 2010-05-05 when following rule on case2 and case 3?

How case1 is evaluated?

Also what exactly mean by "1. Data type determination of the resulting expression - ISNULL uses the first parameter type, COALESCE follows the CASE expression rules and returns type of value with highest precedence"

Thanks in advance
Gopi
Post #983024
Posted Wednesday, September 29, 2010 6:13 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, November 17, 2013 11:53 AM
Points: 623, Visits: 237
Really Good one.
Post #995687
Posted Wednesday, March 7, 2012 2:56 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:52 PM
Points: 483, Visits: 244
Nice question on the COALASCE function that ignores the data type and size hence returning the entire string. Thanks
Post #1263307
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse