जगदीश खोलिया: Handling Division By Zero Scenarios in SQL using NULLIF

Tuesday, December 13, 2011

Handling Division By Zero Scenarios in SQL using NULLIF


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
*/

No comments: