SQL – How To Avoid Divide By Zero Error

When you try to divide an integer (or float etc.) by a zero value in SQL it throws a Divide by zero error.

This example illustrates the error:

declare @intA as int
declare @intB as int

set @intA = 50
set @intb = 0

Select @intA / @intB

Message:

Msg 8134, Level 16, State 1, Line 7
Divide by zero error encountered.

The NULLIF statement can be used here to avoid the devide by zero error and return a null value instead.

declare @intA as int
declare @intB as int

set @intA = 50
set @intb = 0

Select @intA / nullif(@intB, 0)

 

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