Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ISNULL


ISNULL

Author
Message
JestersGrind
JestersGrind
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3519 Visits: 1585
Comments posted to this topic are about the item ISNULL



bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6265 Visits: 25280
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
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8860 Visits: 11739
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
dgvozdetsky
dgvozdetsky
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2382 Visits: 113
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
archie flockhart
archie flockhart
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1263 Visits: 1150
Fails on MONEY type as well (using SQL2000)
michael.kaufmann
michael.kaufmann
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1297 Visits: 1082
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 :coolSmile.

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
archie flockhart
archie flockhart
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1263 Visits: 1150
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.
michael.kaufmann
michael.kaufmann
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1297 Visits: 1082
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 :-P and continue the learning process by following the explanations given.

Just my 2 cents,
Michael
TomThomson
TomThomson
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11421 Visits: 12091
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

gary.mazzone
gary.mazzone
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2575 Visits: 687
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search