Set decimal precision in SQL CLR function in C#

user_12264965 0
Viewed 319

I have a SQL CLR function written in C# that goes something like:

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString Decimal(decimal sum)
    {
        return sum.ToString();
    }
}

And SQL Server assumes that 'sum' is a numeric(18,0) type.

Is there a way to change the precision and scale of an input parameter in C#?

sqlclr sql-server c#
2 Answers

The precision and scale can be specified using SQLFacet attributes. For example, [Microsoft.SqlServer.Server.SqlFacet(Precision = ..., Scale = ...)] ...

user_71652790

While allmhuran is not incorrect regarding SqlFacet, please understand that SqlFacet, at least for the Precision and Scale properties (and at least some, but maybe not all, other properties) is used by Visual Studio / SSDT to auto-generate the DLL for publishing. Meaning, you do not need to rely on SqlFacet if you write your own CREATE statements. Using SSDT to do this is great for initial development / testing, but SqlFacet doesn't support all the options you might want to set, such as parameter defaults and/or something like WITH RETURNS NULL ON NULL INPUT (used for Scalar UDFs). For example:

  • CREATE FUNCTION [schema_name].[function_name]
(
  @param1 NVARCHAR(MAX),
  @param2 DECIMAL(17, 9)
)
RETURNS NVARCHAR(MAX)
WITH EXECUTE AS CALLER,
     RETURNS NULL ON NULL INPUT
AS EXTERNAL NAME [assembly].[class].[method];

  • CREATE PROCEDURE [schema_name].[proc_name]
    @param1 NVARCHAR(4000) = N'default value',
    @param2 DECIMAL(17, 9) = 123.456789
WITH EXECUTE AS CALLER
AS EXTERNAL NAME [assembly].[class].[method];

ALSO:

  • If you are creating the objects manually, you will still need the VARBINARY literal representation of the DLL for the CREATE ASSEMBLY statement (the assumption being that Visual Studio is used to compile the assembly but not using it / SSDT to generate the publish SQL script). For this you can use a simple, open-source command line utility I wrote: BinaryFormatter

  • You should be using SqlDecimal as the input type instead of decimal. You then get the actual decimal value from the param using the Value property:

    public static SqlString Decimal([SqlFacet(Precision = ..., Scale = ..)] 
SqlDecimal sum)
{
   decimal theValue = sum.Value;
}

For more info on working with SQLCLR in general, please visit: SQLCLR Info

user_5777650
Related