# 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
Limbeck

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
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
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
Avoid int, it types the return as having no decimal places.
thats fine...use my same code, but change the data types to decimal

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?

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%?

yes
chapmandew

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.