Need to pass decimal(18,2) to a CLR function...

• darius.england

  Mr or Mrs. 500

  Points: 557

  More actions

  March 1, 2011 at 10:06 am

  #246313

  Hi. I have a pretty simple piece of C# code:

  [SqlFunction(IsDeterministic = true, Name = "ufn_RunningTotalForDecimal")]

  public static SqlDecimal RunningTotalsDecimal(SqlDecimal val, SqlByte id, SqlDecimal nullValue)

  {

  string dataName = string.Format("MulstiSqlRt_{0}_{1}", typeof(SqlByte).FullName, id.IsNull ? 0 : id.Value);

  object lastSum = CallContext.GetData(dataName);

  SqlDecimal total = lastSum != null ? (SqlDecimal)lastSum : SqlDecimal.Null;

  if (!val.IsNull)

  total = total.IsNull ? val : total + val;

  else

  total = total.IsNull ? nullValue : (nullValue.IsNull ? total : total + nullValue);

  CallContext.SetData(dataName, total);

  return total;

  }

  }

  The problem with this is that when deployed it appears as a function that takes decimal(18,0) for the val and nullValue parameters. I want to have decimal(18,2) instead. How do I change the function above to achieve this?

  Thanks.

  Best,

  Darek

• Tonci Korsano

  Mr or Mrs. 500

  Points: 571

  More actions

  March 1, 2011 at 10:14 am

  #1293064

  hi there,

  you might want to try this --> [Microsoft.SqlServer.Server.SqlFacet(Precision = 18, Scale = 2)]SqlDecimal val, [Microsoft.SqlServer.Server.SqlFacet(Precision = 18, Scale = 2)]SqlDecimal nullVal

  i haven't ever tried this, but sqldecima data type has a precision property as well.

  take a look here --> http://www.google.com/#sclient=psy&hl=en&q=sqldecimal+precision&aq=2&aqi=&aql=&oq=&pbx=1&bav=on.1,or.&fp=19d8439716407a1a

  good luck,

  tonci korsano

• darius.england

  Mr or Mrs. 500

  Points: 557

  More actions

  March 1, 2011 at 10:30 am

  #1293074

  Hi there. Well, it works for the parameters but it doesn't for the outcome, which is marked as illegal when I try to decorate it with the facet... Any other suggestions, please?

• Tonci Korsano

  Mr or Mrs. 500

  Points: 571

  More actions

  March 1, 2011 at 10:41 am

  #1293079

  hi again,

  i haven't used sqldecimal ever, but if precision property didn't work, you might want to try method SqlDecimal.ConvertToPrecScale() in your return value.

  here is an example of this method --> http://msdn.microsoft.com/en-us/library/system.data.sqltypes.sqldecimal.converttoprecscale(VS.80).aspx

  in general, go here --> http://www.google.com/#hl=en&sugexp=ldymls&xhr=t&q=sqldecimal+return+value+precision&cp=33&pf=p&sclient=psy&aq=f&aqi=&aql=&oq=sqldecimal+return+value+precision&pbx=1&bav=on.1,or.&fp=19d8439716407a1a

  it looks like that other programmers had to get around to similar problems you have.

  good luck,

  tonci korsano

• darius.england

  Mr or Mrs. 500

  Points: 557

  More actions

  March 1, 2011 at 11:52 am

  #1293113

  Well, I have found the solution. In short:

  [SqlFunction(IsDeterministic = true, Name = "ufn_RunningTotalForDecimal")]

  [return: Microsoft.SqlServer.Server.SqlFacet(Precision = 18, Scale = 2)]

  public static SqlDecimal RunningTotalsDecimal(

  [Microsoft.SqlServer.Server.SqlFacet(Precision = 18, Scale = 2)]

  SqlDecimal val,

  SqlByte id,

  [Microsoft.SqlServer.Server.SqlFacet(Precision = 18, Scale = 2)]

  SqlDecimal nullValue

  )

  {

  string dataName = string.Format("MulstiSqlRt_{0}_{1}", typeof(SqlByte).FullName, id.IsNull ? 0 : id.Value);

  object lastSum = CallContext.GetData(dataName);

  SqlDecimal total = lastSum != null ? (SqlDecimal)lastSum : SqlDecimal.Null;

  if (!val.IsNull)

  total = total.IsNull ? val : total + val;

  else

  total = total.IsNull ? nullValue : (nullValue.IsNull ? total : total + nullValue);

  CallContext.SetData(dataName, total);

  return total;

  }

  }

  That required me to purchase membership on http://www.experts-exchange.com....

  Best,

  Darek