Processing tips for zero divisor in SQL Server

When we do division in SQL Server, we often need to deal with a divisor of zero, because if you encounter this situation, SQL Server will throw an exception with zero divisor error , we do not want to display this exception to the user.

Do an example that will report this error, such as the following SQL code:

declare @a int;
declare @b int;
Set @a=10
Set @b=0
Select @a/@b

Run throws an error that encounters a divide-by-zero error

This chapter focuses on how to handle the two methods of dividing the divisor to zero in SQL Server:
One, use case statements.
We rewrite the SQL above, and when @b=0, we return 1. Rewrite as follows:

declare @a int;
declare @b int;
Set @a=10
Set @b=0
Select Case @b If 0 then 1 else @a/@b end

This writing is certainly correct, but the trouble is obvious, let's look at the second approach.

Second, use the nullif function .
The nullif function has two parameters, which are defined as follows:
Nullif (expression, expression)
The function is: If two specified expressions are equal, a null value is returned.
Look at an example:

Select Nullif (0,0)

Run Result: null

We know that null and any number of operations, the result is equal to NULL, we can use this to rewrite the above SQL to:

declare @a int;
declare @b int;
Set @a=10
Set @b=0
Select @a/nullif (@b,0)

The result of the operation is naturally null.

Using the IsNull function again, we can implement the @b=0 when the result returns 1 of the demand. The final SQL rewrite is as follows:

declare @a int;
declare @b int;
Set @a=10
Set @b=0
Select IsNull (@a/nullif (@b,0), 1)

OK, two methods are introduced, relative to method one, I would recommend that you use method two, because the code of method two is more omission.

(If you have friends who don't understand the IsNull function, here's an explanation.)
IsNull is defined as follows:
IsNull (parameter 1, parameter 2)
The effect is that when parameter 1 equals null, the value of parameter 2 is returned. )

Processing tips for zero divisor in SQL Server