ISNULL

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 Greg Goodge » ISNULL

29 posts, Page 1 of 3

1 2 3 »»»

ISNULL

Rate Topic

Display Mode

Topic Options

Author

Message

JestersGrind

Posted Tuesday, November 22, 2011 8:52 PM

Hall of Fame

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:42 PM
Points: 3,487, Visits: 1,581

Comments posted to this topic are about the item ISNULL

Post #1210656

bitbucket-25253

Posted Tuesday, November 22, 2011 8:54 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 5:32 AM
Points: 5,101, Visits: 20,196

Thanks for the question

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read
Before posting a performance problem please read

Post #1210657

Hugo Kornelis

Posted Wednesday, November 23, 2011 12:58 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 5:18 AM
Points: 5,233, Visits: 7,028

Interesting question. My gut feeling was that either the conversion to money or none of the conversions would be problematic for the empty string; I am glad I took the time to investigate instead of taking a 50% chance between those two options.

Too bad the explanation is incomplete. Everything in the explanation was already known (to me, that is). The only thing that was new for me was also the only thing that the question's author did not explain - "for some reason" is not a real explanation in my book.
Anyway, since I investigated this before answering I can now affirm that this behaviour is explicitly documented in the CAST and CONVERT topic in Books Online.

Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Post #1210772

dgvozdetsky

Posted Wednesday, November 23, 2011 2:23 AM

SSCommitted

Group: General Forum Members
Last Login: Wednesday, January 30, 2013 6:06 AM
Points: 1,685, Visits: 90

Interesting question, but when i execute bit modified this script, i receive other error:

declare
  @decimal decimal(18,6),
  @bit bit,
  @float float,
  @integer int,
  @money money

select IsNull(@decimal, '')
select isnull(@bit, '')
select isnull(@integer, '')
select isnull(@money, '')
select isnull(@float, '')

So error was next:

Msg 257, Level 16, State 3, Line 11
Implicit conversion from data type varchar to money is not allowed. Use the CONVERT function to run this query.

I responded to this question as script will fail for SELECT IsNULL(@money, ''), but my answer is wrong Sad

Post #1210801

archie flockhart

Posted Wednesday, November 23, 2011 3:25 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, May 17, 2013 5:13 AM
Points: 1,123, Visits: 986

Fails on MONEY type as well (using SQL2000)

Post #1210828

michael.kaufmann

Posted Wednesday, November 23, 2011 4:06 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, February 06, 2013 11:51 PM
Points: 1,263, Visits: 1,079

Hugo Kornelis (11/23/2011)

[...] Anyway, since I investigated this before answering I can now affirm that this behaviour is explicitly documented in the CAST and CONVERT topic in Books Online.

Thanks to JestersGrind for an interesting question (got it right by researching before answering as by gutt feeling I'd have also said that none would fail Cool

).

And a great big thank you to Hugo for the link to BOL.

In case anyone gets stuck at the conversion table (which indicates implicit conversion between character and numeric data types), keep on reading to find this little comment towards the end:
SQL Server also returns an error when an empty string (" ") is converted to numeric or decimal..

PS: Found this blog by Craig Freedman quite interesting.

-Michael

Post #1210852

archie flockhart

Posted Wednesday, November 23, 2011 4:22 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, May 17, 2013 5:13 AM
Points: 1,123, Visits: 986

In the BOL, I still don't see anything that goes beyond "for some reason" as an explanation of why you can't convert an empty string to decimal, while you can convert it to integer or float or bit. Anyone have any ideas ?

Also, maybe this is maybe not the place to debate this, but is "researching" the question before answering really in the spirit of the QOTD ? Is it any different to just copying the code and running it ?

When I get one of the (many) questions where I don't know the answer, I prefer to try to work out what I think a likely answer would be. I can look at the explanations later, but often learn more by getting it wrong and then finding out why.

Post #1210863

michael.kaufmann

Posted Wednesday, November 23, 2011 5:08 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, February 06, 2013 11:51 PM
Points: 1,263, Visits: 1,079

archie flockhart (11/23/2011)

Also, maybe this is maybe not the place to debate this, but is "researching" the question before answering really in the spirit of the QOTD ? Is it any different to just copying the code and running it ?

Yes, it is. In order to tick the right answer(s) after running the code you don't even have to understand why something happened the way it did.
When I research a question, I 1) have to make sure I understand the question (not having English as my mother tongue adds some difficulties sometimes Smile

) and 2) by then answering the question I can verify whether the research results I came up with are correct, or whether I've picked the wrong path.
Personally I think that the spirit of the QotD is to learn and whether the learning occurs before or after answering the question is not so important for me.

When I get one of the (many) questions where I don't know the answer, I prefer to try to work out what I think a likely answer would be. I can look at the explanations later, but often learn more by getting it wrong and then finding out why.

I guess that depends on what kind of learning type you are; I prefer to investigate for myself--and get some extra approval by finding out that the author used the same source as I found the most relevant.
Often enough I'd still get it wrong Tongue

and continue the learning process by following the explanations given.

Just my 2 cents,
Michael

Post #1210879

L' Eomot Inversé

Posted Wednesday, November 23, 2011 5:17 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 5:11 AM
Points: 7,081, Visits: 7,129

It's a good question. And the explanation, despite Hugo's comment, is just fine (except that the reference that Hugo gives would have been useful).

Hugo Kornelis (11/23/2011)

That explicit documentation doesn't give any explanation either. Since MONEY and DECIMAL and NUMERIC are all exact numeric types (see the Data Types topic in BoL) with decimal fractions, there should be some justification for the different behaviours but no reason is given anywhere for this difference in behaviour; why does MONEY, and exact numeric with decimal fraction, behave in this respect like the exact numerics that don't have decimal fractions instead of like the other exact numeric types with decimal fractions? I think that "for some reason" is the only reason that can be offered on the basis of BoL (although of course "because the people who invented this MONEY kludge hadn't a clue" might be some people's guess as to the reason); of course it is not necessary to offer a reason in explanations for QoTD - and in some cases (as illustrated by the potential guessed explanation suggested above) it may better not to. Why don't we have a reason? Not because the question author failed in his explanation - indeed he very accurately (albeit somewhat obliquely) pointed out that there is no documented reason - but because MS has not chosen to tell us the reason (of course there's no reason why it should, although for technical decisions which look this bizarre most technology companies do give reasons).

Tom
Que conclure à la fin de tous mes longs propos? C'est que les préjugés sont la raison des sots. (Voltaire, 1756)

Post #1210883

gary.mazzone

Posted Wednesday, November 23, 2011 6:03 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 12:45 PM
Points: 1,536, Visits: 461

If you comment out Select ISNULL(@Money,'') you get the error on Select ISNULL(@Decimal,'')

[color=#red]Error converting data type varchar to numeric.[/color]

So shouldn't the answer be more then 1?

Post #1210916

« Prev Topic | Next Topic »

29 posts, Page 1 of 3

1 2 3 »»»

Permissions