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.
No related posts.