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()
