SQL Server:Case语句显示Msg 8115，将varchar转换为数据类型numeric时发生算术溢出错误

Question

我有以下正在抛出的查询：

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

查询：

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

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

我还注意到，对于Then子句中的以下类型的数据，没有显示错误。

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

所以只有对于Then子句中的值0.00或0.0，我才会得到错误。

我还测试了以下查询，工作良好：

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

更有趣的是，根据表中的数据，Case语句的Then部分永远不会被执行。请帮助我理解Case声明的这种行为。

Answer 1

我对此进行了研究，结果如下：

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

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

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

这是来自Microsoft的关于大小写表达式的返回类型(https://msdn.microsoft.com/en-us/library/ms181765.aspx)：

返回中的一组类型中优先级最高的类型result_expression和可选的else_result_express。了解更多信息有关信息，请参阅数据类型优先级（Transact-SQL）。

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

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

Answer 2

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

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

• 当Fee实际上为NULL时，返回0.00——一个数值
• 当Fee（varchar）不为NULL时，则返回该值-字符串

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

出于某种原因，在Fee不是NULL的情况下，这似乎有时会失败。

因此，关键是：只要可能，就从CASE语句中的所有可能值中返回相同的数据类型，并显式地（使用CAST或CONVERT），不要强迫SQL Server为您处理

Answer 3

代码将重现您的问题

DECLARE @Fee MONEY
DECLARE @test VARCHAR
SELECT @Fee = ISNULL(@test, 0.00)
Select @fee

但这个是修复

DECLARE @Fee MONEY
DECLARE @test VARCHAR
SELECT @Fee = ISNULL(@test, '0.00')
Select @fee