SQL Rounding Up Issue

often people complain about rounding up decimal values. The first question they ask is why SQL Server truncates the decimals which is silly because it happens in the code-behind.

For instance, say that you have a field of decimal datatype with precision 5 and scale 2 – decimal(5, 2)
Now if you don’t define precision of the output parameter the value will be rounded up. e.g. 0.99 becomes 1

CREATE PROCEDURE mySP
(
     @MyDecValue DECIMAL(5, 2) output
)
AS
     SELECT @MyDecValue = MyDecField FROM MyTable WHERE Foo=1
Dim command As SqlCommand = connection.CreateCommand
command.CommandText = "mySp"
command.Parameters.Add("@MyDecValue", SqlDbType.Decimal, 5).Direction = Output

now if you execute the command the value will be as mentioned rounded up .. meaning, 0.99 becomes 1

You can solve it if you Define the precision and scale of the parameter e.g.

Dim command As SqlCommand = connection.CreateCommand
command.CommandText = "mySp"
Dim decimalsparam As New SqlParameter
decimalsparam.Direction = ParameterDirection.Output
decimalsparam.ParameterName = "@MyDecValue"
decimalsparam.Precision = 5
decimalsparam.Scale = 2
decimalsparam.Size = 5
 
command.Parameters.Add(decimalsparam)
command.ExecuteNonQuery()

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>