Supressing Divide by Zero error

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...
fkmfkmAsked:
Who is Participating?
 
JamesTConnect With a Mentor Commented:
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
 
dmeiliCommented:
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
 
fkmfkmAuthor Commented:
I thought i can set something like ansi warning off to just ignore this error ?
0
 
JamesTCommented:
Well, you have to use SET ARITHABORT OFF. If you have a divide by zero error it will return NULL.
0
 
fkmfkmAuthor Commented:
I can't create a udf cause i am using 7.0
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.