Arithmetic overflow error converting numeric

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Arithmetic overflow error converting numeric

Author

Topic

gagani
Posting Yak Master

112 Posts

Posted - 2015-01-13 : 10:31:00

The following function is throwing the error:
1) "Arithmetic overflow error converting numeric to data type
numeric."

2) The variable @x should only be set when if condition is equal to 1. For any other values, it should not go inside if condition. Does the following if condition code meet that criteria?

ALTER FUNCTION [dbo].[fn_Calculator]
(
@abc bit
)
returns decimal(14,10)
as begin
declare @x decimal(14,10)
declare @Result decimal(14,10)

if(@abc=1)
begin
set @x = 8730/0.1628640000
set @Result = @x
end

Return @Result
end

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-13 : 11:32:22

1. The result has 5 digits before the decimal point, but you've only allowed for 4
2. yes.

gagani
Posting Yak Master

112 Posts

Posted - 2015-01-13 : 15:35:48

Thank you for your reply. decimal(15,10) has resolved.
For 2) does that verifies null/blank/empty,etc, I mean all kinds of values which throws exceptions.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-13 : 16:22:08

You declared @abc to be of type bit, If anyone calls the function with another data type, SQL will try to convert it to a bit. It may fail, e.g.

select cast ('abc' as bit)

returns

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'abc' to data type bit.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources