sql 除以_避免SQL除以零错误的方法

sql 除以

This article explores the SQL divide by zero error and various methods for eliminating this.

本文探讨了SQL除以零错误以及消除该错误的各种方法。

介绍 (Introduction)

We all know that in math, it is not possible to divide a number by zero. It leads to infinity:

众所周知，在数学中不可能将数字除以零。 它导致无限：

Source: www.1dividedby0.com

资料来源： www.1dividedby0.com

If you try to do in calculator also, you get the error message – Cannot Divide by zero:

如果您也尝试在计算器中执行此操作，则会收到错误消息–无法除以零：

We perform data calculations in SQL Server for various considerations. Suppose we perform an arithmetic division operator for calculating a ratio of products in a store. Usually, the division works fine, and we get the ratio:

出于各种考虑，我们在SQL Server中执行数据计算。 假设我们执行算术除法运算符来计算商店中产品的比率。 通常，该除法效果很好，我们得到的比率为：

DECLARE @Product1 INT;
    DECLARE @Product2 INT;
    SET @Product1 = 50;
    SET @Product2 = 10;
    SELECT @Product1 / @Product2 ProductRatio;

Someday, the product2 quantity goes out of stock and that means we do not have any quantity for product2. Let’s see how the SQL Server query behaves in this case:

总有一天，product2的数量缺货，这意味着我们没有product2的任何数量。 让我们看看在这种情况下SQL Server查询的行为：

DECLARE @Product1 INT;
    DECLARE @Product2 INT;
    SET @Product1 = 50;
    SET @Product2 = 0;
    SELECT @Product1 / @Product2 ProductRatio;

We get SQL divide by zero error messages (message id 8134, level 16):

我们得到SQL除以零错误消息（消息ID 8134，级别16）：

We do not want our code to fail due to these errors. It is a best practice to write code in such a way that it does not give divide by zero message. It should have a mechanism to deal proactively with such conditions.

我们不希望我们的代码由于这些错误而失败。 最好的做法是，编写代码时不要给出除以零的信息。 它应该有一种主动处理这种情况的机制。

SQL Server provides multiple methods for avoiding this error message. Let’s explore it in the next section.

SQL Server提供了多种方法来避免出现此错误消息。 让我们在下一部分中进行探讨。

方法1：SQL NULLIF函数 (Method 1: SQL NULLIF Function)

We use NULLIF function to avoid divide by zero error message.

我们使用NULLIF函数来避免被零除的错误消息。

The syntax of NULLIF function:

NULLIF函数的语法：

NULLIF(expression1, expression2)

It accepts two arguments.

它接受两个参数。

• If both the arguments are equal, it returns a null value

  如果两个参数相等，则返回空值

  For example, let’s say both arguments value is 10:

  例如，假设两个参数的值均为10：

  SELECT NULLIF(10, 10) result;
  

  In the screenshot, we can see that the output is null:

  在屏幕截图中，我们可以看到输出为null：

  

• If both the arguments are not equal, it returns the value of the first argument

  如果两个参数都不相等，则返回第一个参数的值

  In this example, both argument values differ. It returns the output as value of first argument 10:

  在此示例中，两个参数值都不同。 它返回输出作为第一个参数10的值：

  SELECT NULLIF(10, 5) result;
  

  

Let’s modify our initial query using the SQL NULLIF statement. We place the following logic using NULLIF function for eliminating SQL divide by zero error:

让我们使用SQL NULLIF语句修改初始查询。 我们使用NULLIF函数放置以下逻辑，以消除SQL除以零的错误：

• Use NULLIF function in the denominator with second argument value zero
  在分母中使用NULLIF函数，第二个参数值为零
• If the value of the first argument is also, zero, this function returns a null value. In SQL Server, if we divide a number with null, the output is null as well
  如果第一个参数的值也为零，则此函数返回空值。 在SQL Server中，如果我们将数字除以null，则输出也将为null
• If the value of the first argument is not zero, it returns the first argument value and division takes place as standard values
  如果第一个参数的值不为零，则返回第一个参数值，并且除法作为标准值进行

DECLARE @Product1 INT;
        DECLARE @Product2 INT;
        SET @Product1 = 50;
        SET @Product2 = 0;
        SELECT @Product1 / NULLIF(@Product2,0) ProductRatio;

Execute this modified query. We can see the output NULL because denominator contains value zero.

执行此修改后的查询。 我们可以看到输出NULL，因为分母包含零值。

Do we want null value in the output? Is there any method to avoid null and display a definite value?

我们是否要在输出中使用null值？ 有什么方法可以避免null并显示确定的值？

Yes, we can use SQL ISNULL function to avoid null values in the output. This function replaces the null value in the expression1 and returns expression2 value as output.

是的，我们可以使用SQL ISNULL函数来避免输出中出现空值。 此函数替换expression1中的空值，并返回expression2值作为输出。

Let’s explore the following query with a combination of SQL NULLIF and SQL ISNULL function:

让我们结合SQL NULLIF和SQL ISNULL函数探索以下查询：

• First argument ((@Product1 / NULLIF(@Product2,0)) returns null
  第一个参数（（@ Product1 / NULLIF（@ Product2,0））返回null
• We use the ISNULL function and specify the second argument value zero. As we have the first argument null, the output of overall query is zero (second argument value)
  我们使用ISNULL函数并将第二个参数值指定为零。 由于第一个参数为null，因此整体查询的输出为零（第二个参数值）

DECLARE @Product1 INT;
        DECLARE @Product2 INT;
        SET @Product1 = 50;
        SET @Product2 = 0;
        SELECT ISNULL(@Product1 / NULLIF(@Product2,0),0) ProductRatio;

方法2：使用CASE语句避免除以零错误 (Method 2: Using CASE statement to avoid divide by zero error)

We can use a CASE statement in SQL to return values based on specific conditions. Look at the following query. It does the following task with the Case statement.

我们可以在SQL中使用CASE语句根据特定条件返回值。 查看以下查询。 它使用Case语句执行以下任务。

The Case statement checks for the value of @Product2 parameter:

Case语句检查@ Product2参数的值：

• If the @Product2 value is zero, it returns null
  如果@ Product2值为零，则返回null
• If the above condition is not satisfied, it does the arithmetic operation (@Product1/@Product2) and returns the output
  如果不满足上述条件，则执行算术运算（@ Product1 / @ Product2）并返回输出

方法3：将ARITHABORT设置为OFF (Method 3: SET ARITHABORT OFF)

We can use set methods to control query behavior. By default, SQL Server has a default value of SET ARITHABORT is ON. We get SQL divide by zero error in the output using the default behavior.

我们可以使用set方法来控制查询行为。 默认情况下，SQL Server的默认值SET ARITHABORT为ON。 我们使用默认行为在输出中获得SQL除以零错误。

The T-SQL syntax for controlling the ARITHABORT option is shown below:

用于控制ARITHABORT选项的T-SQL语法如下所示：

SET ARITHABORT { ON | OFF }

• Using ARITHABORT ON, the query will terminate with divide by zero message. It is the default behavior. For this demo, let’s enable it using the SET ARITHABORT ON statement:

  使用ARITHABORT ON，查询将以除以零的消息终止。 这是默认行为。 对于此演示，让我们使用SET ARITHABORT ON语句启用它：

  SET ARITHABORT ON   -- Default 
          SET ANSI_WARNINGS ON
          DECLARE @Product1 INT;
          DECLARE @Product2 INT;
          SET @Product1 = 50;
          SET @Product2 = 0;
          SELECT @Product1 / @Product2 ProductRatio;
  

  We get the SQL divide by zero error messages:

  我们得到SQL除以零错误消息：

  

• Using ARITHABORT OFF, the batch will terminate and returns a null value. We need to use ARITHABORT in combination with SET ANSI_WARNINGS OFF to avoid the error message:

  使用ARITHABORT OFF，批处理将终止并返回空值。 我们需要将ARITHABORT与SET ANSI_WARNINGS OFF结合使用，以避免出现错误消息：

  

  We can use the following query to check the current setting for the ARITHABORT parameter:

  我们可以使用以下查询来检查ARITHABORT参数的当前设置：

  DECLARE @ARITHABORT VARCHAR(3) = 'OFF';  
      IF ( (64 & @@OPTIONS) = 64 ) SET @ARITHABORT = 'ON';  
      SELECT @ARITHABORT AS ARITHABORT;
  

  The default ARITHABORT setting for SSMS is ON. We can view it using SSMS Tools properties. Navigate to Tools -> Options -> Advanced:

  SSMS的默认ARITHABORT设置为ON。 我们可以使用SSMS Tools属性查看它。 导航到工具->选项->高级：

  

  Many client applications or drivers provide a default value of ARITHABORT is OFF. The different values might force SQL Server to produces a different execution plan, and it might create performance issues. You should also match the setting similar to a client application while troubleshooting the performance issues.

  许多客户端应用程序或驱动程序提供的默认值ARITHABORT为OFF。 不同的值可能会强制SQL Server生成不同的执行计划，并且可能会导致性能问题。 在对性能问题进行故障排除时，还应将设置与客户端应用程序相匹配。

  Note: You should not modify the value of ARITHABORT unless required. It might create performance issues, as well. I would suggest using alternative methods (as described earlier) for avoiding SQL divide by zero error.

  注意：除非有必要，否则您不应修改ARITHABORT的值。 它还可能会导致性能问题。 我建议使用替代方法（如前所述）来避免SQL除以零错误。

结论 (Conclusion)

In this article, we explored the various methods for avoiding SQL divide by zero error. It is best practice to be proactive and use these mechanisms so that code does not fail in real-time.

在本文中，我们探索了各种避免SQL除以零错误的方法。 最佳实践是积极主动并使用这些机制，以使代码不会实时失败。

翻译自: https://www.sqlshack.com/methods-to-avoid-sql-divide-by-zero-error/

sql 除以