0

I have the following query which is throwing:

Arithmetic overflow error converting varchar to data type numeric.

Query:

Select 
    @Fee = Case 
              When IsNull(Fee, '') = '' Then 0.00 
              Else Fee 
           End

@Fee is of type Money, and Fee is Varchar type.

I have also observer that for following types of data in Then clause no error is being displayed.

Select @Fee = Case When IsNull(Fee, '') = '' Then 1 Else Fee End
Select @Fee = Case When IsNull(Fee, '') = '' Then 1.0 Else Fee End

So only for values 0.00 or 0.0 in Then clause I am getting error.

I have also tested with below query and worked fine:

Select @Fee = Case When IsNull(Fee, '') = '' Then Cast(0.00 as money) Else Fee End

And more interesting thing is that, as per data we have in table, Then part of the Case statement will never be executed. Please help me understanding this behavior of Case statement.

Improve this question
2
  • does Fee is column ? If it is than please give sample data of fee column.. Commented Dec 17, 2015 at 9:08
  • Yes @Dhaval Fee is column of type Varchar and all i have is value 2 in the column Commented Dec 17, 2015 at 9:21

3 Answers 3

Reset to default
2

I have played around this and this is what happens:

DECLARE @v VARCHAR(20) = '1'
SELECT CASE WHEN '' <> '' THEN 0.00 ELSE @v END col1 INTO tempTable

When you will execute the above query you will see error but the table will be created and the type of the column created col1 is numeric(2,2). If you change to 0.0000 the type will be numeric(4,4). This means that actually the type of an expression depends on that value. Also (2,2) means that you can store only values with length 2 and everything goes after dot(.12, .25 etc). So it can not cast 1.00 to numeric(2,2) because the type doesn't allow to have digits before dot.

The best rule here is to always return the same types from different paths of case expression.

This is from Microsoft about return type of case expression (https://msdn.microsoft.com/en-us/library/ms181765.aspx):

Returns the highest precedence type from the set of types in result_expressions and the optional else_result_expression. For more information, see Data Type Precedence (Transact-SQL).

This is about type precedence where you can see that numeric precedes varchar(https://msdn.microsoft.com/en-us/library/ms190309.aspx). So the return type of your case expression becomes numeric(2,2) and this is the answer to your question.

I will also give you an advise: never store money values in varchar columns. Always store values in appropriate type(there are so many types available that all your needs will be satisfied).

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

You have a CASE expression that returns two different datatypes - that's always a really bad idea....

Select 
    @Fee = Case 
              When IsNull(Fee, '') = '' Then 0.00 
              Else Fee 
           End
  • When Fee is in fact NULL, you return 0.00 - a numerical value
  • When Fee (varchar) is NOT NULL, then you return that value - a string

Since both cases are assigned to one and the same @Fee variable - SQL Server must coerce these into the same datatype - whatever @Fee dictates (money in your case).

And for some reason, in the case of Fee being NOT NULL, that seems to fail at times.

So the point is: whenever possible, return the same datatype from all your possible values in a CASE statement - and do so explicitly (using a CAST or CONVERT) - don't force SQL Server to handle this for you

Improve this answer

1 Comment

Thanks @marc_s, but when i am using 1.0 instead of 0.00 error is not being displayed. Please help me to understand this behavior on the basis of data.
1

the code will reproduce your issue

DECLARE @Fee MONEY
DECLARE @test VARCHAR

SELECT @Fee = ISNULL(@test, 0.00)

Select @fee

but this one is the fix

DECLARE @Fee MONEY
DECLARE @test VARCHAR

SELECT @Fee = ISNULL(@test, '0.00')

Select @fee
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.