SQL Server：Case语句显示消息8115，算术溢出错误将varchar转换为数据类型numeric

I have the following query which is throwing:

我有以下查询抛出：

Arithmetic overflow error converting varchar to data type numeric.

将varchar转换为数据类型numeric的算术溢出错误。

Query:

查询：

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

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

@Fee是Money类型，而Fee是Varchar类型。

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

我还观察到，在Then子句中跟踪数据类型没有显示错误。

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.

因此，只有在Then子句中的值为0.00或0.0时，我才会收到错误。

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.

更有趣的是，根据我们在表中的数据，然后部分Case语句将永远不会被执行。请帮我理解Case语句的这种行为。

3 个解决方案

#1

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.

当您执行上述查询时，您将看到错误，但将创建表，并且创建列col1的类型为数字（2,2）。如果更改为0.0000，则类型将为数字（4,4）。这意味着实际上表达式的类型取决于该值。另外（2,2）意味着你只能存储长度为2的值，并且一切都在点（.12，.25等）之后。所以它不能将1.00转换为数字（2,2），因为该类型不允许在点之前有数字。

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

这里最好的规则是始终从case表达式的不同路径返回相同的类型。

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

这是来自Microsoft关于案例表达式的返回类型（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).

返回result_expressions中的类型集和可选的else_result_expression中的最高优先级类型。有关更多信息，请参见数据类型优先级（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.

这是关于类型优先级，您可以在其中看到数字在varchar之前（https://msdn.microsoft.com/en-us/library/ms190309.aspx）。因此，case表达式的返回类型将变为数字（2,2），这就是您的问题的答案。

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).

我还会给你一个建议：永远不要在varchar列中存储货币值。始终以适当的类型存储值（有许多类型可用，满足您的所有需求）。

#2

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

你有一个CASE表达式返回两种不同的数据类型 - 这总是一个非常糟糕的主意....

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
如果费用实际为NULL，则返回0.00 - 数值
When Fee (varchar) is NOT NULL, then you return that value - a string
当Fee（varchar）为NOT NULL时，则返回该值 - 字符串

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).

由于两种情况都分配给同一个@Fee变量 - SQL Server必须将这些变量强制转换为相同的数据类型 - 无论@Fee指示什么（在您的情况下为金钱）。

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

由于某种原因，在费用为非NULL的情况下，这似乎有时会失败。

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

所以重点是：尽可能从CASE语句中的所有可能值返回相同的数据类型 - 并明确地（使用CAST或CONVERT）执行此操作 - 不要强制SQL Server为您处理此问题

#3

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

#1