Using SqlFacet Attributes for an SQL function
-
Hi, I wrote a function in C# to be used as a scalar function in SQL2005: [Microsoft.SqlServer.Server.SqlFunction(IsPrecise = true, IsDeterministic = true, DataAccess = DataAccessKind.Read)] public static SqlDecimal GetTaxAmountFromTax(SqlString tax, SqlDecimal amount) { SqlConnection conn = new SqlConnection("context connection=true"); SqlDataAdapter adap = new SqlDataAdapter("SELECT * FROM TransactionTaxes", conn); DataTable taxTable = new DataTable(); adap.Fill(taxTable); DataRow[] rows = taxTable.Select("[ID] = '" + tax.ToString() + "'"); if (rows.Length == 0) return SqlDecimal.Null; if (rows[0]["CalculatedOnTax"] != DBNull.Value) { if ((bool)rows[0]["Cumulative"]) { DataRow[] rows1 = taxTable.Select("[ID] = '" + rows[0]["CalculatedOnTax"].ToString() + "'"); if (rows1.Length == 0) return SqlDecimal.Null; return ((amount + (amount * (decimal)rows1[0]["Rate"]) / new SqlDecimal(100))) * (decimal)rows[0]["Rate"] / new SqlDecimal(100); } else return amount * (decimal)rows[0]["Rate"] / new SqlDecimal(100); } else return amount * (decimal)rows[0]["Rate"] / new SqlDecimal(100); } I want to publish this function making sure the return type has a precision of 2 and a scale of 18. I found that I have to use the SqlFacetAttributes but I am unsure how to do it for a function. I found examples only for a Strored Procedure parameter. Any help appreciated. Txs