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 123»»»

ISNULL Expand / Collapse
Author
Message
Posted Tuesday, November 22, 2011 8:52 PM


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: 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
Posted Tuesday, November 22, 2011 8:54 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
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
Posted Wednesday, November 23, 2011 12:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 3:56 PM
Points: 6,086, Visits: 8,354
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
Posted Wednesday, November 23, 2011 2:23 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 8:29 AM
Points: 2,158, Visits: 106
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
Post #1210801
Posted Wednesday, November 23, 2011 3:25 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 10, 2014 9:11 AM
Points: 1,155, Visits: 1,055
Fails on MONEY type as well (using SQL2000)
Post #1210828
Posted Wednesday, November 23, 2011 4:06 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 9, 2013 11:12 PM
Points: 1,263, Visits: 1,081
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 ).

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
Posted Wednesday, November 23, 2011 4:22 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 10, 2014 9:11 AM
Points: 1,155, Visits: 1,055
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
Posted Wednesday, November 23, 2011 5:08 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 9, 2013 11:12 PM
Points: 1,263, Visits: 1,081
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 ) 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 and continue the learning process by following the explanations given.

Just my 2 cents,
Michael
Post #1210879
Posted Wednesday, November 23, 2011 5:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:00 PM
Points: 7,847, Visits: 9,596
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)
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.

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
Post #1210883
Posted Wednesday, November 23, 2011 6:03 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:53 AM
Points: 1,952, Visits: 550
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 »

Add to briefcase 123»»»

Permissions Expand / Collapse