Please help to prevent "Divide By Zero Error" in SQL 3

[beetlebailey]

Hopefully this is a simple question. I am using the following error formula within Excel 2000 which prevents the #DIV/0! from showing up in the spread sheet:
=IF(ISERROR(SUM(C3:C12)),0,SUM(C3:C12))

Now, I am trying to query a SQL server database with the following query and receive a "Divide by zero error":
Select Hour1/Tact1 as PCT1 from vpd where line=505

Is there a similar way to prevent such errors from occurring in SQL server 2000 ?

Thanks, "beetle

 

[checkai]

Select case when isnull(Tact1,0) = 0 then 0 else Hour1/Tact1 end as PCT1 from vpd where line=305

"...we both know I'm training to become a cagefighter...see what happens if you try 'n hit me..."

 

[Pattycake245]

Add this before your query and include it when running:

SET ARITHABORT OFF

Which means that the query will not terminate once it hits a divide by zero or overflow error.

Tim

 

[vongrunt]

SET ARITHABORT OFF works always only if ANSI_WARNINGS are turned off:

set arithabort off

-- works, returns NULL
set ansi_warnings off
select 2/0

-- blam!
set ansi_warnings on
select 2/0

In complex batches it is recommended to reverse these settings back after OFF is no more necessary.

Another way is to replace 0 with NULL. Then division will also return NULL:

Select Hour1/NULLIF(Tact1, 0) as PCT1 from vpd where line=505

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)