Gert-Jan Strik
3/20/2007 7:29:00 PM
Wow, that is a lot of code for such a simple task...
rodchar,
One of the simples solutions is to do something like this:
SELECT @v1 / NULLIF( @v2, 0)
This will return NULL if division was invalid (Division by zero) or else
it will give the proper result.
If you prefer the result of an invalid division to be zero, you can add
a COALESCE:
SELECT COALESCE( @v1 / NULLIF(@v2,0), 0)
HTH,
Gert-Jan
sloan wrote:
>
> I usually do this:
>
> HOWEVER, you need to watch for inputs, outputs , rounding, and
> if a N/0 is ok to return a "0".
>
> -- CREATE FUNCTION
> ALTER FUNCTION dbo.udfSafeDivision (@v1 decimal ( 10 , 2 ) , @v2 decimal (
> 10 , 2 ) )
>
> RETURNS
> decimal ( 10 , 2 )
>
> AS
>
> BEGIN
>
> declare @returnValue decimal ( 10 , 2 )
>
> if (@v2=0) -- No Division by Zero
> BEGIN
> select @returnValue = 0
> END
> else
> BEGIN
> select @returnValue = convert(decimal ( 10 , 2 ) , (convert(decimal
> ( 10 , 2 ) , @v1) / convert(decimal ( 10 , 2 ) , @v2) ) )
> END
>
> return @returnValue
>
> END
>
> "rodchar" <rodchar@discussions.microsoft.com> wrote in message
> news:A6FBFE9D-A8D7-43BE-A1C8-341E172C1165@microsoft.com...
> > hey all,
> > i have a stored procedure that takes 2 variables and divides them. what's
> > the best method to handle the exception of dividing by zero?
> >
> > thanks,
> > rodchar