I'm having some unexpected results when working with SqlDecimals. In the end it seems to boil down to scale issues when dividing 2 SqlDecimals.
c# example code looks like this :
[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;
}
SQL test code looks like this :
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 select 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 web I've found plenty of issues related to rounding errors when converting SqlDecimal to Decimal, but in my case everything is SqlDecimal from begin to end as I wanted to avoid exactly that. Likewise I would 'hope' the result would be identical to how this got implemented natively in SQL.. but alas. Even when it doesn't overflow it will give me different results in 'border-cases'.
Anyone has any tips on how to fix this or at least work around it ? Does the SqlDecimal internally use (.Net) Decimals and hence can't cram the information in it's 3 bytes ?!?!?
PS: I can't 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 SQLCLR version of my original UDF (which does a lot more than just divide btw =). Admittedly, it is faster all-right, but it should work for the same range of numbers.
