为什么 SQL Server 在运行存储过程中抛出"divide by 0"，并且不会在独立语句中发生

Question

此代码仅在运行存储过程时将"除以0"错误，并且不会在独立语句中发生。

我想知道执行作为存储过程和通用陈述的区别导致了这种情况。

表PPB_PBExecuteLine有大约200个记录，并且具有67个记录为0，但是只有选择才能在指定情况下获得QTY = 0记录

这是语句：

--exec PPB_P_ReCalcActaulExeSUMToPB 1001603230260241,-1,1001605170110011
Create Procedure PPB_P_ReCalcActaulExeSUMToPB
    @Org Bigint,
    @CBS Bigint,
    @ProjectIDs Nvarchar(max) 
with recompile
as
Begin
    Declare @SQL Nvarchar(Max)
    If Object_ID('tempdb..#Projects') is not null
            drop table #Projects;
    Create table #Projects (ID bigint null);
    If(@ProjectIDs != '')
    Begin
        Set @SQL = 'insert into #Projects (ID)' + 'select ' + replace(@ProjectIDs,',',' as ID union all select ') 
        Exec sp_executesql @SQL
    End
    If object_id('tempdb..#ProjectBudget_Detail_DiffCost') Is Not Null
        Drop Table #ProjectBudget_Detail_DiffCost;
    --*******this statement
    Select 
        B.ID as ExecuteLine,
        SUM(C.ExecuteCost - (B.OutExeCome / B.Qty) * C.Qty) as DiffCost
    Into 
        #ProjectBudget_Detail_DiffCost
    From 
        PPB_PBExecute A
    Inner Join 
        PPB_PBExecuteLine B on A.ID = B.PBExecute
    Inner Join 
        PPB_PBExecuteDetial C on C.PBExecuteLine = B.ID 
    Where 
        A.Org = @Org 
        and (A.CBS = @CBS or @CBS <= 0) 
        and (A.Project in (select ID from #Projects) or @ProjectIDs = '')
    Group by 
        B.ID
    Update A 
    Set A.DiffCost = B.DiffCost 
    From PPB_PBExecuteLine as A 
    Inner Join #ProjectBudget_Detail_DiffCost as B on A.ID = B.ExecuteLine
   --and more 
End

Answer 1

从我在存储过程中看到的内容，只有一行会导致异常，这在

中

SUM(C.ExecuteCost - (B.OutExeCome / B.Qty) * C.Qty ) as DiffCost

当B.Qty为0时，它将引发异常。但是，可以通过在CASE表达式中添加列中添加额外验证来预防这一点。

SUM(C.ExecuteCost - (B.OutExeCome / (CASE WHEN B.Qty = 0 THEN 1 ELSE B.Qty END) * C.Qty) as DiffCost

Answer 2

这很复杂。但是，首先，使用nullif()消除问题的可能性：

Select B.ID as ExecuteLine,
       SUM( C.ExecuteCost-(B.OutExeCome/nullif(B.Qty, 0))*C.Qty ) as DiffCost

(或者您可能想要其他逻辑。(

一种可能性是B.Qty实际上是0引起问题。让我推测这不是问题，因为您在一个地方看到错误，而不是另一个地方。

这导致0在被过滤的行上的可能性。如何在被过滤的行中获得错误？好吧，SQL Server不能保证表达式处理顺序。实际上，它可以在where中的过滤之前推动一些计算。

瞧！错误。

为什么这会发生在一个地方而不是另一个地方？那将是由于执行计划的不同。也许存储过程中的版本被缓存，最佳计划已更改。

Answer 3

在数学上除以零是不确定的。因此，在SQL Server中，也将是未定义的，这意味着它不是整数也不是空的。

在您的存储过程中，

SUM(C.ExecuteCost - (B.OutExeCome / B.Qty) * C.Qty) as DiffCost

如果b.qty = 0，将会丢弃错误，因此您需要使用以下一种方法来解决此问题：

使用案例：

您可以使用案例senario，并用数字或null替换零值。在这里，我将用数字1替换。

(CASE WHEN B.Qty <> 0 THEN B.Qty ELSE 1 )

使用nullif

这是最快的方法，如果值等于零，它将返回null。

NULLIF(B.Qty, 0)

ARITHABORT和ANSI_WARNINGS

您可以设置Arithabort和ANSI_WARNINGS，这样，SQL Server将以零部门返回NULL。

SET ARITHABORT OFF
SET ANSI_WARNINGS OFF