sqlDecimal to decimal clr stored procedure Unable to cast object of type 'System.Data.SqlTypes.SqlDecimal' to type 'System.IConvertible'

Question

I'm new to this. I'm writing a clr stored procedure that calls another stored procedure to get a value that's used in a calculation.

A stored procedure returns int (I guess SqlInt32? the type of the value in the table is int) that is then needed to be converted to decimal.

Here's the code I have:

public static int CalculateMyValues(SqlDecimal aMyValue, SqlString aMyValueType, out SqlDecimal aResult)
{
    aResult = 0;
    try
    {
        SqlConnection conn = new SqlConnection("context connection=true");
        SqlCommand cmd = new SqlCommand("sp_GetType", conn);
        cmd.Parameters.AddWithValue("@myValueType", aMyValueType);
        cmd.CommandType = CommandType.StoredProcedure;
        conn.Open();

        object type = cmd.ExecuteScalar();
        
        conn.Close();

        decimal typeForCalculation = Convert.ToDecimal(type);
        decimal value = Convert.ToDecimal(aMyValue); // **

        Calculations calc = new Calculations(); 
        decimal result = calc.DoCalculation(typeForCalculation, value);

I get an exception (I think there see ******):

Unable to cast object of type 'System.Data.SqlTypes.SqlDecimal' to type 'System.IConvertible'.

Any ideas?

Answer 1

The problem is that the Sql types don't implement the IConvertible interface, which is what Convert uses. You need to cast the type to a SqlDecimal, then use the Value property to obtain the decimal representation:

decimal typeForCalculation = ((SqlDecimal)type).Value;
decimal value = aMyValue.Value;