Help with UDF and math - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help with UDF and math

Author

Topic

Darkmatter5
Starting Member

17 Posts

Posted - 2012-06-14 : 16:07:18

Here's my UDF code:


ALTER FUNCTION [ml52252].[P2UDF3] 
(
  -- Add the parameters for the function here
  @Price money,
  @Discount int
)
RETURNS money
AS
BEGIN
  -- Declare the return variable here
  DECLARE @Result money

  -- Add the T-SQL statements to compute the return value here
  IF (@Price IS NULL) OR (@Discount IS NULL)
    SET @Result = NULL
  ELSE
    SET @Result = @Price*(1-@Discount/100)

  -- Return the result of the function
  RETURN @Result
END

If I run the UDF with 18 as @Price and 3 as @Discount I get a result of 18.00, by my math it should be 17.46.

Any ideas on why it's doing this?
Thanks!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-06-14 : 16:18:28

Use "@Discount/100.0" or "@Discount/$100" instead of "Discount/100". Integer division will ignore decimal places and cause the rounding you're seeing.

And you're better off using the following expression in your query:

CASE WHEN @Price IS NULL OR @Discount IS NULL THEN Null
ELSE @Price*(1-@Discount/$100) END AS Result

The UDF will cause severe performance degradation on a large number of rows.

Darkmatter5
Starting Member

17 Posts

Posted - 2012-06-14 : 16:19:08

Oh it's because SQL is performing Integer math and not accounting for the decimals. How should I go about making sure SQL accounts for the decimals in the equation?

Darkmatter5
Starting Member

17 Posts

Posted - 2012-06-14 : 16:20:44

Got it, thanks!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-06-14 : 16:21:55

I made an edit to my original reply. For something that simple it's better to avoid using a UDF.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-06-14 : 17:06:07

You don't need the case statement, because a null input parameter will return a null result.

It would be better to do this as inline code; a scalar UDF would be much slower.

select
	a.*, Result = a.Price*($1.00-(a.Discount*$.01))
from
	(  -- Test Data
	select	price = $18,	Discount = 3	union all
	select	price = null,	Discount = 3	union all
	select	price = $18,	Discount = null
	) a

Results:

                price    Discount                Result
--------------------- ----------- ---------------------
                18.00           3                 17.46
                 NULL           3                  NULL
                18.00        NULL                  NULL

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources