Handling Division By Zero Scenarios in T-SQL

Source : http://www.sqlmusings.com/2009/05/09/handling-division-by-zero-scenarios-in-t-sql/

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: