我写了一个函数来处理我的存储过程:

print 'Creating safeDivide Stored Proc ...'
go

if exists (select * from dbo.sysobjects where  name = 'safeDivide') drop function safeDivide;
go

create function dbo.safeDivide( @Numerator decimal(38,19), @divisor decimal(39,19))
   returns decimal(38,19)
begin
 -- **************************************************************************
 --  Procedure: safeDivide()
 --     Author: Ron Savage, Central, ex: 1282
 --       Date: 06/22/2004
 --
 --  Description:
 --  This function divides the first argument by the second argument after
 --  checking for NULL or 0 divisors to avoid "divide by zero" errors.
 -- Change History:
 --
 -- Date        Init. Description
 -- 05/14/2009  RS    Updated to handle really freaking big numbers, just in
 --                   case. :-)
 -- 05/14/2009  RS    Updated to handle negative divisors.
 -- **************************************************************************
   declare @p_product    decimal(38,19);

   select @p_product = null;

   if ( @divisor is not null and @divisor <> 0 and @Numerator is not null )
      select @p_product = @Numerator / @divisor;

   return(@p_product)
end
go

我写了一个函数来处理我的存储过程:

print 'Creating safeDivide Stored Proc ...'
go

if exists (select * from dbo.sysobjects where  name = 'safeDivide') drop function safeDivide;
go

create function dbo.safeDivide( @Numerator decimal(38,19), @divisor decimal(39,19))
   returns decimal(38,19)
begin
 -- **************************************************************************
 --  Procedure: safeDivide()
 --     Author: Ron Savage, Central, ex: 1282
 --       Date: 06/22/2004
 --
 --  Description:
 --  This function divides the first argument by the second argument after
 --  checking for NULL or 0 divisors to avoid "divide by zero" errors.
 -- Change History:
 --
 -- Date        Init. Description
 -- 05/14/2009  RS    Updated to handle really freaking big numbers, just in
 --                   case. :-)
 -- 05/14/2009  RS    Updated to handle negative divisors.
 -- **************************************************************************
   declare @p_product    decimal(38,19);

   select @p_product = null;

   if ( @divisor is not null and @divisor <> 0 and @Numerator is not null )
      select @p_product = @Numerator / @divisor;

   return(@p_product)
end
go

There is no magic global setting 'turn division by 0 exceptions off'. The operation has to to throw, since the mathematical meaning of x/0 is different from the NULL meaning, so it cannot return NULL. I assume you are taking care of the obvious and your queries have conditions that should eliminate the records with the 0 divisor and never evaluate the division. The usual 'gotcha' is than most developers expect SQL to behave like procedural languages and offer logical operator short-circuit, but it does NOT. I recommend you read this article: http://www.sqlmag.com/Articles/ArticleID/9148/pg/2/2.html

SELECT Dividend / ISNULL(NULLIF(Divisor,0), 1) AS Result from table

通过使用nullif()捕获零，然后使用isnull()捕获结果为零，可以避免除零错误。

这似乎是解决我的情况的最佳方法，当我试图解决除零的问题时，这种情况确实发生在我的数据中。

假设您想要计算各个学校俱乐部的男女比例，但是您发现下面的查询失败了，并且在试图计算指环王俱乐部的比例时出现了一个除零的错误，因为指环王俱乐部没有女性:

SELECT club_id, males, females, males/females AS ratio
  FROM school_clubs;

可以使用NULLIF函数来避免除零。NULLIF比较两个表达式，如果相等则返回null，否则返回第一个表达式。

将查询重写为:

SELECT club_id, males, females, males/NULLIF(females, 0) AS ratio
  FROM school_clubs;

任何数字除以NULL都会得到NULL，并且不会产生错误。

我是这样解决的:

IIF(ValueA != 0, Total / ValueA, 0)

它可以被包装在一个更新中:

SET Pct = IIF(ValueA != 0, Total / ValueA, 0)

或者在选择中:

SELECT IIF(ValueA != 0, Total / ValueA, 0) AS Pct FROM Tablename;

想法吗?