using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Diagnostics;
using System.Text;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[SqlFunction(DataAccess = DataAccessKind.Read)]
public static SqlDecimal GetCustomPrice(SqlGuid Item_Key, SqlGuid Company_Key)
{
SqlDecimal retvalue = new SqlDecimal();
try
{
StringBuilder sqlstr = new StringBuilder();
sqlstr.Append("SELECT A.Price ");
sqlstr.Append("FROM Price A ");
sqlstr.Append("JOIN Link_Table B ");
sqlstr.Append("ON A.Price_Key = B.FKey ");
sqlstr.Append("JOIN Lookup C ");
sqlstr.Append("ON B.Lookup_Key = C.Lookup_Key ");
sqlstr.Append("AND C.Group_Name = 'Price_Type' ");
sqlstr.Append("AND C.Value = 'Custom' ");
sqlstr.Append("JOIN Item D ");
sqlstr.Append("ON B.PKey = D.Item_Key ");
sqlstr.Append("JOIN Link_Table E ");
sqlstr.Append("ON A.Price_Key = E.FKey ");
sqlstr.Append("JOIN Lookup F ");
sqlstr.Append("ON E.Lookup_Key = F.Lookup_Key ");
sqlstr.Append("AND F.Group_Name = 'Price_Type' ");
sqlstr.Append("AND F.Value = 'Custom' ");
sqlstr.Append("JOIN Company G ");
sqlstr.Append("ON E.PKey = G.Company_Key ");
sqlstr.Append("WHERE D.Item_Key = @.Item_Key ");
sqlstr.Append("AND G.Company_Key = @.Company_Key");
using (SqlConnection conn = new SqlConnection("context connection=true;"))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sqlstr.ToString(), conn);
SqlDataAdapter adap = new SqlDataAdapter(cmd);
SqlParameter para = new SqlParameter();
para.ParameterName = "@.Item_Key";
para.SqlDbType = SqlDbType.UniqueIdentifier;
para.SqlValue = Item_Key;
cmd.Parameters.Add(para);
para = new SqlParameter();
para.ParameterName = "@.Company_Key";
para.SqlDbType = SqlDbType.UniqueIdentifier;
para.SqlValue = Company_Key;
cmd.Parameters.Add(para);
DataSet ds = new DataSet();
adap.Fill(ds);
if (ds.Tables.Count > 0)
{
if (ds.Tables[0].Rows.Count > 0)
{
retvalue = SqlDecimal.ConvertToPrecScale(new SqlDecimal((decimal)ds.Tables[0].Rows[0]["Price"]),12,3);
}
}
}
}
catch (Exception e)
{
throw e;
}
return SqlDecimal.ConvertToPrecScale(retvalue, 12, 3);
}
};
The value in the record is a decimal 12,3 which is 0.950 and when it is returned from this UDF it is 1. Using the debuger the value is 0.950 until it gets to the calling program were it
is 1. I have looked for an answer all over the place with not much luck Can anyone help I will be in their debt.
Thanks
Larry
If you are deploying from Visual Studio, you can use SqlFacet to specify the precision and scale of your return value by adding another attribute to your method definition like so:
[return: SqlFacet(Precision=12, Scale=3)]
Otherwise, you specify the Precision and Scale as part of CREATE FUNCTION:
CREATE FUNCTION GetCustomPrice(...) RETURNS decimal(12,3) ...
Steven
No comments:
Post a Comment