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

  • 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

  • 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

  • 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?

  • 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

  • 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

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

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