Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
unclesamis@cox.net
Starting Member
18 Posts |
 Posted - 2011-01-17 : 14:07:03
|
| The following query views a data type of an integer, having a value of zero, to have a value of nothing.DECLARE @var INTSET @var = 0SELECT @var = CASE WHEN (@var = '') THEN NULL ELSE @var ENDSELECT @varThis causes me problems in some of the queries that I have seen in the environment I work in.Is there a setting that will keep zero as zero, as type int, and not interpret as a value of nothing? |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-01-17 : 15:06:55
|
| Your empty string will implicitly cast to a zero. So your case statement is assigning NULL to it.The code is silly. Don't compare integers to strings and you'll get the result you expect. If you MUST compare them, then explicitly cast any values first, to avoid ambiguity. |
 |
|
|
unclesamis@cox.net
Starting Member
18 Posts |
Posted - 2011-01-17 : 15:29:36
|
| I don't have an empty string. I'm using an integer of zero.The case statement is assigning NULL if the value in the variable equals ''.My point is I don't believe that 0 should = ''; 0 should = 0 and nothing else.If the variable is changed to an integer value other than 0 then it is interpreted otherwise.SELECT CASE WHEN 0 = '' THEN 'True' ELSE 'False' ENDSELECT CASE WHEN 0 = 0 THEN 'True' ELSE 'False' ENDSELECT CASE WHEN 1 = '' THEN 'True' ELSE 'False' END |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-01-17 : 15:33:00
|
| '' is an empty string and you are comparing it to a number.JimEveryday I learn something that somebody else already knew |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-01-17 : 15:52:09
|
quote:
My point is I don't believe that 0 should = ''; 0 should = 0 and nothing else.
Then use an explicit cast as Russell stated. You can't control SQL Server's implicit casting mechanism, you can only work around it. |
|
|
|
unclesamis@cox.net
Starting Member
18 Posts |
Posted - 2011-01-17 : 17:22:55
|
| quote:"Don't compare integers to strings and you'll get the result you expect"I didn't expect this. 1 didn't equal an empty string; I would expect that. I just wasn't initially thinking about the string being implicitly cast to an integer in this comparison.I just see it caused me an issue and have found other posting examples where this lead to confusion.The reason I have code like that is because the application passes an empty string to the database procedure and I needed for the empty strings to be converted then to NULL. |
|
|
|
unclesamis@cox.net
Starting Member
18 Posts |
Posted - 2011-01-17 : 17:23:58
|
| Thanks everyone. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
Why is zero = '' ?