CODEWITHSUNDEEP
sql-server
Sumit Gupta
Sumit Gupta

Reputation: 2192

SQL UDF : Floating point error

I just wrote a SQL Function as below

Create Function LAeq(@Frequency Decimal(18,2), @Sound Decimal(18,2)) 
 RETURNS DECIMAL(18,2)
 AS
 BEGIN
declare @T1 Decimal (28, 10)
DECLARE @T2 Decimal (28, 10)
DECLARE @T3 Decimal (28, 10)
DECLARE @T4 Decimal (28, 10)
DECLARE @T5 Decimal (28, 10)
DECLARE @T6 Decimal (28, 10)
DECLARE @T7 Decimal (28, 10)

SET @T1 = POWER(12200.0,2) * POWER(@Frequency, 4)
SET @T2 = POWER (@Frequency, 2) + POWER(20.6,2)
SET @T3 = POWER (@Frequency, 2) + POWER(107.7,2)
SET @T4 = POWER (@Frequency, 2) + POWER(737.9,2)
SET @T5 = POWER (@Frequency, 2) + POWER(12200.0,2)
SET @T6 = SQRT(@T3 *@T4) * @T5 * @T2
SET @T7 = @T1/ @T6
SET @T7 = @Sound + (2.0 + (20.0 * LOG10(@T7)))
RETURN @T7
END

This function works if I pass value 0.8, 26. However it fails when I have value like 20000, 80. my function should work for Frequency as max of 20K and Sound can be less than 100 as max values. I try to change the T1, T7 from Decimal (28,10) to 30, 15 or 18,2 but nothing works for me. I don't want to define the limit on data type as when I wrote a SQL statement with same formula as 

Select 26 + (2 + (20 * LOG10((POWER(12200,2) * POWER(0.8, 4))/ (SQRT((POWER (0.8, 2) + POWER(107.7,2)) * (POWER (0.8, 2) + POWER(737.9,2))) * (POWER (0.8, 2) + POWER(12200,2)) * (POWER (0.8, 2) + POWER(20.6,2))))))

OR

Select 80 + (2 + (20 * LOG10((POWER(12200,2) * POWER(20000.00, 4))/ (SQRT((POWER (20000.00, 2) + POWER(107.7,2)) * (POWER (20000.00, 2) + POWER(737.9,2))) * (POWER (20000.00, 2) + POWER(12200,2)) * (POWER (20000.00, 2) + POWER(20.6,2))))))

it works pretty fine and more accurate than my function. can I eliminate the need of data type in function? If not how can ensure maximum accuracy?

Thanks.

Upvotes: 0

Views: 72

Answers (1)

FrankPl
FrankPl

Reputation: 13315

I would use float instead of decimal as the data type for the input parameters and local variables, this should not cause overflows for reasonable input values.

Upvotes: 1

Related Questions