CODEWITHSUNDEEP
sql-serverado.net
bbsimonbb
bbsimonbb

Reputation: 29002

What is rounding my values in DECIMAL columns?

I have a SQL update request. I want it to only modify the columns for which a value is supplied in the update model, so I use the general form myCol = ISNULL(@myParam, myCol). Here is the full SQL...

update Justif set 
DateTransaction = ISNULL(@dateTransaction,DateTransaction),
Cif = ISNULL(@cif,Cif),
NomFournisseur = ISNULL(@nomFournisseur,NomFournisseur),
MontantHT = ISNULL(@montantHT,MontantHT),
MontantTtc = ISNULL(@montantTtc,MontantTtc),
TauxTva = ISNULL(@tauxTva,TauxTva),
MontantTva = ISNULL(@montantTva,MontantTva),
ReceptionNumber = ISNULL(@receptionNumber,ReceptionNumber),
Locked = IIF(@locked > 0,GETDATE(),null),
Used = IIF(@used is not null, @used, Used),
NatureOcr = ISNULL(@natureOcr, NatureOcr)
where JustifID = @justifId

Now, the weirdest thing, at one point the app uses this request just to set the column Used.

The montantTtc parameter, like all the others, is initialized with DBNull.Value (and the SqlDbType set to decimal), then to my great surprise, the decimal columns are rounded to the nearest int.

What am I not understanding about ISNULL()?

enter image description here

Upvotes: 1

Views: 252

Answers (1)

Jeroen Mostert
Jeroen Mostert

Reputation: 28779

Parameter type inference strikes again. A Decimal SQL parameter that's NULL is passed as a DECIMAL(29,0). ISNULL returns the type of its first parameter, which takes care of the rest. Short code snippet to reproduce/prove this:

using (var connection = new SqlConnection(@"Data Source=(localdb)\MSSQLLocalDB")) {
    connection.Open();
    using (var command = new SqlCommand(@"
        DECLARE @v DECIMAL(4,1) = 123.4; 
        SELECT 
            ISNULL(@p, @v),
            SQL_VARIANT_PROPERTY(ISNULL(@p, @v), 'Precision'), 
            SQL_VARIANT_PROPERTY(ISNULL(@p, @v), 'Scale')"
    )) {
        command.Connection = connection;
        command.Parameters.Add(new SqlParameter("@p", SqlDbType.Decimal)).Value = DBNull.Value;
        using (var reader = command.ExecuteReader()) {
            reader.Read();
            Console.WriteLine($@"
                Munged value: {reader.GetValue(0)}
                Precision: {reader.GetValue(1)}
                Scale: {reader.GetValue(2)}
            ");
        }
    }
}

Munged value: 123
Precision: 29
Scale: 0

The proper fix is to supply the Precision and Scale of the parameter according to the column. An alternative is to use

COALESCE(@p, @v)

which is equivalent to an expression of the form

CASE WHEN @p IS NOT NULL THEN @p ELSE @v END

Both will apply the rules of DECIMAL promotion (which results in a DECIMAL(30,1)). Note that this is possibly unsafe if the source type has a lot of precision: using DECLARE @v DECIMAL(17,10) = 123.0123456789 will give a rounded DECIMAL(38,9) of 123.012345679. The only truly general fix is to use the exact type of the column.

Upvotes: 2

Related Questions