Unwanted rounding by User Defined Function

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

General SQL Server Forums

New to SQL Server Programming

Unwanted rounding by User Defined Function

Author

Topic

Snamelisch
Starting Member

1 Post

Posted - 2011-12-15 : 06:33:41

In my db I only use colums with datatype Numeric(38,8).

When I run the next select, the result is as expected (this is a strongly simplified version!)

Select convert(numeric(38,8),789012345.12345678 * 1.00000000)
result: 789012345.12345678

When I use a UDF to do this (again this is a stronly simplified version)

ALTER FUNCTION [dbo].[fntest] (@ftest as numeric(38,8))
RETURNS numeric(38,8)
AS
BEGIN
DECLARE @result as numeric(38,8)
Set @result = convert(numeric(38,8),@ftest * 1.00000000)
RETURN @Result
END

And then query the result:
Select [dbo].[fntest](789012345.12345678)
result: 789012345.12345700

I do not know why this is and how to solve it. For me it is not logical.

Marc Schillemans
Senior Data Analist

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 06:44:16

that might be because precision of the product is going beyond maximum scale value (8 digits)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-12-15 : 07:24:07

I don't think so.
This (stupid) test harness returns troubling results:


DECLARE @foo NUMERIC(38,8) = 123123123123123.34231231
DECLARE @one NUMERIC(38,8) = 1.00000000

          SELECT '1.0000 No Cast' AS [test], CAST((@foo * 1.00000000) AS NUMERIC(38,8)) AS [result]
UNION ALL SELECT '1 No Cast', CAST((@foo * 1) AS NUMERIC(38,8))
UNION ALL SELECT '1.00000000 AS numeric', CAST((@foo * CAST(1.00000000 AS NUMERIC(38,8))) AS NUMERIC(38,8))
UNION ALL SELECT 'No sum + Cast result', CAST(@foo AS NUMERIC(38,8))
UNION ALL SELECT 'Vanilla', @foo
UNION ALL SELECT '1.00000000 (Cast to Numeric)', CAST(1.00000000 AS NUMERIC(38,8))
UNION ALL SELECT '2 Variables', CAST(@foo * @one AS NUMERIC(38,8))

results:


test                         result
---------------------------- ---------------------------------------
1.0000 No Cast               123123123123123.34231200
1 No Cast                    123123123123123.34231200
1.00000000 AS numeric        123123123123123.34231200
No sum + Cast result         123123123123123.34231231
Vanilla                      123123123123123.34231231
1.00000000 (Cast to Numeric) 1.00000000
2 Variables                  123123123123123.34231200

So even when you use a sum of two numerics of 38,8 you seem to get a down cast to 6 places of precision?

Server setting?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-12-15 : 11:54:12

When you manipulate Decimal/Numeric vales, SQL has some funky rules about how it changes the precision and scale. I beleive that is what is happening here.

The key is this:

The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.

For reference, here is a link that describes when and how the precision and scale are changed:
http://msdn.microsoft.com/en-us/library/ms190476.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources