SQL Rounding Up Issues

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