I get unexpected results when working with SqlDecimals. In the end, it seems to boil down to scaling problems when splitting 2 SqlDecimals.
Sample C # code is as follows:
[Microsoft.SqlServer.Server.SqlFunction] [return: SqlFacet(Precision = 38, Scale = 8)] public static SqlDecimal fn_divide([SqlFacet(Precision = 38, Scale = 8)]SqlDecimal x, [SqlFacet(Precision = 38, Scale = 8)]SqlDecimal y) { var r = SqlDecimal.Divide(@x, @y); return r; }
The SQL validation code is as follows:
DECLARE @x numeric(38, 8), @y numeric(38, 8) SELECT @x = Replicate('1', 28) + '.12345678', @y = '0.25896314' SELECT via = 'TSQL', x = @x, y = @y, r = Convert(numeric(38, 8), @x / @y) SELECT via = 'SQLCLR', x = @x, y = @y, r = dbo.fn_divide(@x, @y)
The second choice returns me the following error:
A .NET Framework error occurred during execution of user-defined routine or aggregate "fn_divide": ` System.OverflowException: Arithmetic Overflow. System.OverflowException: at System.Data.SqlTypes.SqlDecimal.MultByULong(UInt32 uiMultiplier) at System.Data.SqlTypes.SqlDecimal.AdjustScale(Int32 digits, Boolean fRound) at System.Data.SqlTypes.SqlDecimal.op_Division(SqlDecimal x, SqlDecimal y) at System.Data.SqlTypes.SqlDecimal.Divide(SqlDecimal x, SqlDecimal y) at UserDefinedFunctions.fn_divide(SqlDecimal x, SqlDecimal y)
Searching the Internet I found many problems related to rounding errors when converting SqlDecimal to decimal, but in my case it is all SqlDecimal from beginning to end, as I wanted to avoid this. Similarly, I hope that the result will be identical to how it was originally implemented in SQL .. but alas. Even when it does not overflow, it will give me different results in “borderline cases”.
Does anyone have any tips on how to fix this or at least get around it? Does SqlDecimal use internal (.Net) Decimals and therefore cannot write 3 bytes in it?!?!?
PS: I cannot predict what combinations of numeric (p, s) will be passed to the function, but 38.8 is "acceptable." I was hoping to make a faster version of SQLCLR of my original UDF (which does a lot more than just sharing btw =). Admittedly, it works the fastest, but it should work for the same range of numbers.