Rounding error with SQL CLR extension (user-defined aggregate)
I'm writing some custom .Net extensions for SQL Server 2008. One of them is a user-defined aggregate that should aggregate a set of decimal numbers to a decimal value.
To narrow down my problem, I'm using a simple Const
aggregate, that simply returns a constant decimal value. When adding this as a user-defined aggregate to SQL Server, the values returned are always rounded:
SELECT dbo.Const(n, 2.5) from (select 1 n) x -- returns 3, not 2.5
What am I missing?
Here's the code:
using S开发者_开发知识库ystem;
using System.Data.SqlTypes;
using System.IO;
using Microsoft.SqlServer.Server;
using SqlServer.Clr.Extensions.Aggregates.Implementation;
[Serializable]
[SqlUserDefinedAggregate(Format.UserDefined,MaxByteSize = -1)]
public class Const : IBinarySerialize
{
private decimal _constValue;
public void Init() {}
public void Accumulate(SqlDecimal value, SqlDecimal constValue)
{
_constValue = constValue.Value;
}
public void Merge(Const value) {}
public SqlDecimal Terminate()
{
return new SqlDecimal(_constValue);
}
public void Read(BinaryReader r)
{
_constValue = r.ReadDecimal();
}
public void Write(BinaryWriter w)
{
w.Write(_constValue);
}
}
Compile this code to an assembly named SqlServer.Clr.Extensions.dll
. The following script can be used to add it to SQL Server and verify the unexpected behaviour:
use [MyDb] -- replace with your db name
go
-- drop the Const aggregate if it exists
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Const]'))
DROP AGGREGATE [Const]
GO
-- drop the assembly if it exists
IF EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'SqlServer.Clr.Extensions' and is_user_defined = 1)
DROP ASSEMBLY [SqlServer.Clr.Extensions]
GO
-- import the assembly
CREATE ASSEMBLY [SqlServer.Clr.Extensions]
AUTHORIZATION [dbo]
FROM 'C:\Path\To\SqlServer.Clr.Extensions.dll'
WITH PERMISSION_SET = SAFE
GO
CREATE AGGREGATE [Const](@input decimal, @constValue decimal)
RETURNS decimal
EXTERNAL NAME [SqlServer.Clr.Extensions].[Const] -- put the Const class is in the global namespace
GO
SELECT dbo.Const(n, 2) from (select 1 n) x
SELECT dbo.Const(n, 2.5) from (select 1 n) x
You have to change @constvalue decimal
to @constvalue decimal(13,2)
in
Create aggregate [const](@input decimal, @constvalue decimal)
精彩评论