Try it out..

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Ujar » Try it out..

17 posts, Page 2 of 2

««1 2

Try it out..

Rate Topic

Display Mode

Topic Options

Author

Message

SQLRNNR

Posted Wednesday, August 04, 2010 10:56 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 20, 2013 1:07 PM
Points: 18,733, Visits: 12,332

Thanks for the question.

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

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

adorable_412in

Posted Wednesday, August 04, 2010 10:58 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 6:29 PM
Points: 513, Visits: 82

Good Question. Smile

Post #963640

ziangij

Posted Wednesday, August 04, 2010 10:32 PM

SSCommitted

Group: General Forum Members
Last Login: Friday, May 17, 2013 2:53 AM
Points: 1,529, Visits: 359

nice info about isnull and coalesce... thanks Smile

Post #963953

codebyo

Posted Saturday, August 07, 2010 12:48 PM

SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 16, 2013 5:30 AM
Points: 861, Visits: 1,436

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. Smile

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2

Post #965577

Gopi S

Posted Thursday, September 09, 2010 6:09 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 02, 2013 11:47 AM
Points: 151, Visits: 277

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