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

Question

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Tonci Korsano Mr or Mrs. 500 Points: 571 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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 · Answer 4

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