Prevent Rounding in SQL With Decimal Types

Question

I am working on an exercise that requires me to call a stored procedure within another procedure. The procedure being called simply divides two numbers, however the result seems to be rounded upwards.

In the exercise, 5.0 is divided by 10.0, and the result is 1. The result should obviously be 0.5. The goal is to make the output display correctly without just changing the DECIMAL type.

Here is the code:

GO

CREATE PROCEDURE uspDivide2Numbers
     @decValue1 AS DECIMAL
    ,@decValue2 AS DECIMAL
    ,@decResult AS DECIMAL OUTPUT
AS
SET NOCOUNT ON

SELECT @decResult = @decValue1 / @decValue2

GO


CREATE PROCEDURE uspCallAnotherStoredProcedure
AS
SET NOCOUNT ON

DECLARE @decResult AS DECIMAL

EXECUTE uspDivide2Numbers 5.0, 10.0, @decResult OUTPUT

SELECT @decResult AS decResult

GO

uspCallAnotherStoredProcedure

Any help would be appreciated, I don't really understand why decimal values round in the first place, so it's probably an issue on my end.

Answer 1

The default scale of a decimal is zero, which means that what you have is actually an integer type. So, it's not possible to return the value 0.5 without changing the data type.

If you specify a precision and scale for the type, like decimal(18,2), it can handle numbers that are not integers. The scale is how many digits will be stored after the decimal separator.

---

Depending on your needs, you might also consider the float and real floating point data types.