Re: Division by Zero

#1

try adapting this

declare @a int
declare @b int

set @a = 0
set @b = 1

if (@a = 0 OR @b = 0)
begin
--raiserror('Oops one of the vals is 0',16,1)
select 0 as result
End
Else
select @b /@a

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

<Shane> wrote

Quote:

Hi everybody - I have a problem with a division by zero error message.
Here is some code from a Stored procedure

-----------------------------------
BEGIN
Select @Deleted = Count(Policy_Ref)
from Cashbook where
Policy_Type in ('PC','TW','CV','MC','MR')
and (Record_Type = 'Deleted Trans.')
END

BEGIN
Select @PolCount = Count(Policy_Ref) from Cashbook
WHERE
Policy_Type in ('PC','TW','CV','MC','MR')
and Record_Type = 'New Transactions'
END

SELECT (@PolCount - @Deleted) as PolicyCount, (@LEPolCount -
@LEDeleted) as LEPolicyCount,

convert(numeric,(@LEPolCount - @LEDeleted))/convert(numeric,(@PolCount
- @Deleted)) as Penetration
-----------------------------------

The problem that I have is that either @PolCount OR @LEPolCount may be
zero. This means that I get an error message in SQL Analyser as follows:

Server: Msg 8134, Level 16, State 1, Procedure USER_Penetrations, Line
164
Divide by zero error encountered.

Is there a way to check to see if either value is 0 and then to return a
"friendly" result rather than an error message? By "friendly result", a
zero would be nice.

I know that I can set the variable values to zero and return the values
as zero. However, you will note that I am trying to perform a division
within the Stored Procedure and it is this that is resulting in the
error message. I can perform the calculation outside of the procedure
but I'd like to keep it within it if possible.

Thanks you for your time and consideration.

Shane

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Division by Zero

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Re: Division by Zero - 07-11-2003 , 02:51 AM