为了避免“除零”错误，我们编写了这样的程序:

Select Case when divisor=0 then null
Else dividend / divisor
End ,,,

但这里有一种更好的方式:

Select dividend / NULLIF(divisor, 0) ...

现在唯一的问题是记住NullIf位，如果我使用“/”键。

CREATE FUNCTION dbo.Divide(@Numerator Real, @Denominator Real)
RETURNS Real AS
/*
Purpose:      Handle Division by Zero errors
Description:  User Defined Scalar Function
Parameter(s): @Numerator and @Denominator

Test it:

SELECT 'Numerator = 0' Division, dbo.fn_CORP_Divide(0,16) Results
UNION ALL
SELECT 'Denominator = 0', dbo.fn_CORP_Divide(16,0)
UNION ALL
SELECT 'Numerator is NULL', dbo.fn_CORP_Divide(NULL,16)
UNION ALL
SELECT 'Denominator is NULL', dbo.fn_CORP_Divide(16,NULL)
UNION ALL
SELECT 'Numerator & Denominator is NULL', dbo.fn_CORP_Divide(NULL,NULL)
UNION ALL
SELECT 'Numerator & Denominator = 0', dbo.fn_CORP_Divide(0,0)
UNION ALL
SELECT '16 / 4', dbo.fn_CORP_Divide(16,4)
UNION ALL
SELECT '16 / 3', dbo.fn_CORP_Divide(16,3)

*/
BEGIN
    RETURN
        CASE WHEN @Denominator = 0 THEN
            NULL
        ELSE
            @Numerator / @Denominator
        END
END
GO

当错误传播回调用程序时，可以适当地处理它(如果需要，可以忽略它)。在c#中，SQL中发生的任何错误都会抛出一个异常，我可以捕获并在我的代码中处理，就像任何其他错误一样。

I agree with Beska in that you do not want to hide the error. You may not be dealing with a nuclear reactor but hiding errors in general is bad programming practice. This is one of the reasons most modern programming languages implement structured exception handling to decouple the actual return value with an error / status code. This is especially true when you are doing math. The biggest problem is that you cannot distinguish between a correctly computed 0 being returned or a 0 as the result of an error. Instead any value returned is the computed value and if anything goes wrong an exception is thrown. This will of course differ depending on how you are accessing the database and what language you are using but you should always be able to get an error message that you can deal with.

try
{
    Database.ComputePercentage();
}
catch (SqlException e)
{
    // now you can handle the exception or at least log that the exception was thrown if you choose not to handle it
    // Exception Details: System.Data.SqlClient.SqlException: Divide by zero error encountered.
}

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

我是这样解决的:

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;

想法吗?

使用NULLIF(exp,0)，但以这种方式- NULLIF(ISNULL(exp,0)，0)

如果exp为空，NULLIF(exp,0)将中断，但NULLIF(ISNULL(exp,0)，0)将不会中断