FAQ: How can I specify the precision and scale values of the decimal/numeric return value when deploying a CLR function in Visual Studio?

  • Article

Question

 I have a CLR function with a decimal(18,2) return value. However, I find that the return value becomes to decimal(18,0) after I deployed the UDF in Visual Studio.

 

Answer

 By default, Visual Studio deploys decimal or numeric values with numeric (18,0). There are two methods that you can use to address this issue.

  • A. You can use CREATE FUNCTION statement to change the precision and scale values when creating the function.
  • B. You can also use SqlFacet attribute to specify different precision and scale values when you define your CLR function. In this way, Visual Studio deploys the function with the specific precision and scale.

 

For example, 

    [Microsoft.SqlServer.Server.SqlFunction]

    [return: SqlFacet(Precision = 18, Scale = 2)]

    public static SqlDecimal ReturnDecimal()

    {

        SqlDecimal ReturnValue = new SqlDecimal(1.23);

        return ReturnValue;

    } 

 

More Information

https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.server.sqlfacetattribute.aspx

Applies to 

SQL Server 2005

SQL Server 2008

SQL Server 2008 R2