shelbyinfotech

asked on

# how to calculate percentage in SQL Server without divide by zero error

How can I calculate a percentage in SQL server without getting a divide by zero error when my data contains legitimate zeros. I would like to do this in a UDF

ASKER

Thanks; but I cannot use try/catch here as I am using SQL 2000.

here is the logic....function following.

declare @i int, @j int

select @i = 5, @j = 0

select case when @j = 0 then 0 else @i/@j end

declare @i int, @j int

select @i = 5, @j = 0

select case when @j = 0 then 0 else @i/@j end

create function udf_divide

(

@i int,

@j int

)

returns int

as

begin

declare @k int

set @k = case when @j = 0 or @i = 0 then 0 else @i/@j end

return(@k)

end

(

@i int,

@j int

)

returns int

as

begin

declare @k int

set @k = case when @j = 0 or @i = 0 then 0 else @i/@j end

return(@k)

end

Having to do this in SQL 2K5 and try remember that I have not used any 2K5 specific syntax, so appology if it did. The return result in a divide by zero case I have set as null, but potentially you want something else.

```
CREATE FUNCTION divide
(
@a real,
@b real
)
RETURNS real
AS
BEGIN
DECLARE @result real
SELECT @result = (case when @b = 0 then null else @a/@b end)
RETURN @result
END
```

CREATE FUNCTION Calc_Perc(@Value1 decimal(12,2), @Value2 decimal(12,2))

RETURNS decimal(12,2)

AS

BEGIN

DECLARE @Result decimal(12,2)

SET @Result = CASE WHEN @Value2 = 0 THEN 0 ELSE (@Value1 / @Value2) * 100 END

RETURN @Result

END

RETURNS decimal(12,2)

AS

BEGIN

DECLARE @Result decimal(12,2)

SET @Result = CASE WHEN @Value2 = 0 THEN 0 ELSE (@Value1 / @Value2) * 100 END

RETURN @Result

END

Avoid int, it types the return as having no decimal places.

thats fine...use my same code, but change the data types to decimal

ASKER

Ok Chapmandew; I follow the logic on how to avoid the divide by zero error, however where Otana expanded oh this as to how i would calculate the percentage, it leaves a flawed result in the case of a Zero as Zero times anything is Zero, and would not show a 100% change

what result would you want in that example?

ASKER

I would like to show 100% in either direction in the case of a zero on either side, and a zero for the same values

so, if they are both zero then 0%, but if only 1 then 100%?

ASKER

yes

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

right on the money ...thanks

ASKER

Spoke too soon..my results are integer, should be decimal

instead of multiplying by 100, try 100.00

http://blog.sqlauthority.com/2007/10/03/sql-server-2005-explanation-of-try%E2%80%A6catch-and-error-handling-with-raiseerror-function/