March 29, 2007 at 10:01 am
Can someone tell me if the following behavior is a bug or a SQL “feature” that I’m unaware of?
In the following code, MostRecentValue is a varchar field in a local table variable. I didn’t realize that I was doing a numeric compare on a varchar (…AND MostRecentValue) > 0). Function svfCalculateTrendPercentage expects both params to be varchar. On the call to svfCalculateTrendPercentage, I was getting ‘cannot convert int to varchar’ error.
CASE
WHEN (ISNUMERIC(MostRecentValue) = 1 AND MostRecentValue > 0)
THEN dbo.svfCalculateTrendPercentage(OruValue, MostRecentValue)
ELSE 0
END
And I’m thinking…. WHAT INT??? Finally I figured out that my ‘...AND MostRecentValue > 0’ comparison was apparently implicitly converting MostRecentValue to an int, and leaving it that way! Adding a CONVERT to my test got rid of the error.
CASE
WHEN (ISNUMERIC(MostRecentValue) = 1 AND CONVERT(decimal(15,3), MostRecentValue) > 0)
THEN dbo.svfCalculateTrendPercentage(OruValue, MostRecentValue)
ELSE 0
END
Obviously I should’ve converted it in the first place, but does anyone know the logic behind why SQL would behave this way? Why wouldn't it just alert me that I'm doing something stupid?
March 29, 2007 at 10:44 am
Implicit conversions can get you in many places. The big thing to be aware of is that you'd probably want to compare > 0.0 so that the conversion doesn't take place.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 29, 2007 at 10:50 am
If I used 0.0, why would the conversion not take place? Wouldn't it just convert to a dec instead of an int?
March 30, 2007 at 1:56 pm
There is a whole slew of automatic type conversions that can occur if strict datatyping is not observed. Take a gander at Data Types [SQL Server]: Precedence in BOL.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply