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