Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need to pass decimal(18,2) to a CLR function... Expand / Collapse
Author
Message
Posted Tuesday, March 1, 2011 10:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 2:00 AM
Points: 7, Visits: 86
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
Post #1071397
Posted Tuesday, March 1, 2011 10:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 16, 2013 8:18 AM
Points: 15, Visits: 45
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

Post #1071402
Posted Tuesday, March 1, 2011 10:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 2:00 AM
Points: 7, Visits: 86
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?
Post #1071415
Posted Tuesday, March 1, 2011 10:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 16, 2013 8:18 AM
Points: 15, Visits: 45
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
Post #1071421
Posted Tuesday, March 1, 2011 11:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 2:00 AM
Points: 7, Visits: 86
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 www.experts-exchange.com....

Best,
Darek
Post #1071464
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse