April 27, 2010 at 3:30 pm
I ran into this recently when I was troubleshooting some queries. This is a simple example but I don't understand why the error is occuring.
The following query will error give you the error:
Msg 8115, Level 16, State 8, Line 5
Arithmetic overflow error converting varchar to data type numeric.
declare @p1 varchar(10)
set @p1 = '1.5'
select 'test' =
case
when ISNULL(@p1, 0.0) <> 0.0 then @p1
else 'does not work'
end
This will work:
declare @p1 varchar(10)
set @p1 = '0.5'
select 'test' =
case
when ISNULL(@p1, 0.0) <> 0.0 then @p1
else 'does not work'
end
Why is it that when I change @p1 to '0.5', it works fine! Can someone tell me what I'm missing here? Why would the value 1.5 give me an error but 0.5 doesn't? I am baffled. Thanks!
April 27, 2010 at 3:47 pm
KT8177 (4/27/2010)
I ran into this recently when I was troubleshooting some queries. This is a simple example but I don't understand why the error is occuring.The following query will error give you the error:
Msg 8115, Level 16, State 8, Line 5
Arithmetic overflow error converting varchar to data type numeric.
declare @p1 varchar(10)
set @p1 = '1.5'
select 'test' =
case
when ISNULL(@p1, 0.0) <> 0.0 then @p1
else 'does not work'
end
This will work:
declare @p1 varchar(10)
set @p1 = '0.5'
select 'test' =
case
when ISNULL(@p1, 0.0) <> 0.0 then @p1
else 'does not work'
end
Why is it that when I change @p1 to '0.5', it works fine! Can someone tell me what I'm missing here? Why would the value 1.5 give me an error but 0.5 doesn't? I am baffled. Thanks!
The problem is the ELSE part of your case statement. SQL is trying to convert 'does not work' to an integer and can't.
April 27, 2010 at 3:51 pm
The "does not work" was only a placeholder, I don't think that part matters. I put in the number 1 and it'll still fail, but only if the @p1 is 1.5, but it works for 0.5. That's what i'm not understanding.
April 27, 2010 at 4:04 pm
To make it even more interesting: Add a blank to your check value and it works just fine. Otherwise it will start to fail at '0.95'...
when ISNULL(@p1, '0.0') <> '0.0 ' then @p1
Strange, indeed...
April 27, 2010 at 4:12 pm
It is attempting to do an IMPLICIT data conversion to numeric(1,1) and 1.5 would need to be defined as numeric(2,1). You need to use an explicit data conversion for the comparision.
April 27, 2010 at 4:14 pm
Do you know if the implicit conversion it's doing is converting it to numeric(1,1)? Is that the default?
April 27, 2010 at 4:22 pm
It is based on the 0.0 in the comparision. If you change it to 0.00 it becomes numeric(2,2). Adding a second 0 before the decimal (00.00) doesn't change a thing. You need to use an explicit data conversion on the varchar variable, @p1, to get this to work properly.
April 27, 2010 at 4:36 pm
Lynn Pettis (4/27/2010)
It is attempting to do an IMPLICIT data conversion to numeric(1,1) and 1.5 would need to be defined as numeric(2,1). You need to use an explicit data conversion for the comparision.
That actually explains the tipping point of 0.95: below the value will be rounded to 0.9 and from that point on it will round to 1 - and fails. Thank you Lynn for clarification.
One more great example not to rely on implicit conversion...
April 27, 2010 at 4:48 pm
Thank you for explaining that, it makes sense now!
April 27, 2010 at 9:51 pm
Lynn Pettis (4/27/2010)
It is based on the 0.0 in the comparision. If you change it to 0.00 it becomes numeric(2,2). Adding a second 0 before the decimal (00.00) doesn't change a thing. You need to use an explicit data conversion on the varchar variable, @p1, to get this to work properly.
There's even more than that going on here:
DECLARE @p1 VARCHAR(10);
SET @p1 = '1.5';
SELECT CASE
WHEN ISNULL(@p1, 0.0) <> 0.0 THEN @p1
ELSE 0.0
END
HAVING 1 = 1;
The first issue concerns the data type of the return value of the ISNULL function. For ISNULL, this is the same as that of the check_expression, which in this case is VARCHAR(10). SQL Server converts the literal 0.0 provided as the replacement_value to the ISNULL function to VARCHAR at compilation time, resulting in '0.0' (notice the quotes).
The second issue arises in comparing the result of the ISNULL function to the (second) literal 0.0. The rules of data type precedence prescribe that where data types differ, the data type with lower precedence is converted. To determine which expression has higher precedence, SQL Server must assign a data type to the literal value 0.0 - which results in DECIMAL(1,1) in this case. Decimal has a higher precedence than VARCHAR, so the result of the ISNULL function is now converted to DECIMAL(1,1).
The third issue is what data type to assign to the result of the CASE expression as a whole. The WHEN clause returns VARCHAR(10), whereas the ELSE specifies a constant (which in this case will be typed as DECIMAL(1,1)). Following the data precedence rules, the result of the WHEN clause must therefore be converted to DECIMAL(1,1).
I added a HAVING 1 = 1 to the query so that a 'proper' query plan is produced, where we can see the conversions:
(Scalar Operator
(CASE
WHEN CONVERT_IMPLICIT(numeric(1,1),
isnull([@p1],'0.0'),0)<>(0.0)
THEN CONVERT_IMPLICIT(numeric(1,1),[@p1],0)
ELSE (0.0) END
))
References:
Data Type Precedence (Transact-SQL)
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 5, 2010 at 8:23 am
Hello,
Try this code
declare @p1 varchar(10)
set @p1 = '1.5'
select 'test' =
case
when ISNULL(@p1, 0.0) <> '0.0' then @p1
else 'does not work'
end
This is working. Very simple fact: You have declared the variable data type as varchar and you are comparing it with integer. Thats why it is giving error.
Try this:
Create a table with a column datatype varchar and try to insert a datetime value. You will get error.
Thanks
Rajneesh
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply