Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Supressing Divide by Zero error

Posted on 2002-04-08
Medium Priority
527 Views
I am connection to MS-SQL 7 using ASP.NET.

I use sql statements to do some calculations. Is there a way to suppress the divide by zero error. Instead of error can it return null or zero.?

Thank you...
0
Question by:fkmfkm
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2
• 2

LVL 2

Accepted Solution

JamesT earned 400 total points
ID: 6925489
One way is to check for 0 prior to the calculation. For example:

SELECT SUM(CASE WHEN COLUMNA = 0 THEN 0 ELSE COLUMNB/COLUMNA END)
FROM TABLE_CALCULATION

You could also use a variation on IsNull assuming the column is null and not 0.
0

LVL 1

Expert Comment

ID: 6926014
If you have a number of sql statements doing this it may be worthwhile to create a user defined function such as:

CREATE function dbo.divide_by_zero( @numerator decimal(38,19), @denominator decimal(38,19))
returns decimal(38,19)
as
begin
declare @result decimal (21,2)
if @denominator <= 0
begin
set  @result = 0.00
end
else
begin
set  @result = (@numerator/@denominator)
end
return(@result)
end

0

Author Comment

ID: 6928310
I thought i can set something like ansi warning off to just ignore this error ?
0

LVL 2

Expert Comment

ID: 6928345
Well, you have to use SET ARITHABORT OFF. If you have a divide by zero error it will return NULL.
0

Author Comment

ID: 7035118
I can't create a udf cause i am using 7.0
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
###### Suggested Courses
Course of the Month9 days, 2 hours left to enroll