Sometimes it is inevitable to encounter scenarios that will give division by zero errors

DECLARE @dividend INT
DECLARE @divisor INT
SET @dividend = 1
SET @divisor = 0
SELECT @dividend/@divisor
/* 
Error:
Msg 8134, Level 16, State 1, Line 7
Divide by zero error encountered.
*/

What you can do is you can code around it, so your users and your app do not get this error.
 

Alternative 1: NULLIF (preferred)

The NULLIF built in function returns a NULL if the two parameters are equal. In our case, we want to check if the divisor is zero.

DECLARE @dividend INT
DECLARE @divisor INT
SET @dividend = 1
SET @divisor = 0
SELECT @dividend/NULLIF(@divisor,0)
/* 
Returns NULL
*/

Alternatively, instead of NULL, you may want to display just 0

SELECT ISNULL(@dividend/NULLIF(@divisor,0),0)
/* 
Returns NULL, no error
*/

Alternative 2: CASE

You can also use CASE to drive what values you want to show if the divisor. The downside to this approach is your code can get really lengthy right away by having multiple CASE statements.

SELECT 
	CASE @divisor
	   WHEN 0 THEN 0
	   ELSE @dividend/NULLIF(@divisor,0)
	END
/* 
Returns 0, no error
*/

Alternative 3: IF/ELSE

You can also use IF/ELSE. However this means you cannot just have one SELECT statement. This needs to be in a script, a stored proc, or UDF.

IF @divisor = 0
BEGIN
   SELECT 0
END
ELSE BEGIN
   SELECT @dividend/@divisor
END
/* 
Returns 0, no error
*/

There you go. No more division by zero woes in T-SQL.

Be Sociable, Share!

• 
•