sql server - loss of scale when performing calculation

Question

I'm performing a calculation and I don't get the answer I expect. I lose some scale doing the calculation.

Calc is: 651/1000 * -413.72063274 = -269.33213191 (to 8 d.p)

In SQL Server I do this:

declare @var numeric(28,8)
declare @a numeric(28,8)
declare @b numeric(28,8)

set @var = -413.72063274
set @a   = 651.00000000
set @b   = 1000.00000000

select CAST((@a/@b) * @var as numeric(28,8)) as result_1
     , CAST(CAST(@a as numeric(28,8)) 
      /CAST(@b as numeric(28,8)) as numeric(28,8)) 
      *CAST(@var as numeric (28,8))   as result_2

The results is

result_1: -269.33213200 (correct to 6dp)
result_2 : -269.332132 (correct to 6dp)

How do I get the query to return: -269.33213191 (correct to 8dp)?

Answer 1

The rules for decimal to decimal conversion for multiplication and division are described in BOL.

*The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.

but leaves it unspecified exactly how such truncation is performed. This is documented here. However sometimes it is easier just to use trial and error!

The following intermediate casts give you the desired result. Can you live with those?

DECLARE @var NUMERIC(19,8)
DECLARE @a NUMERIC(19,8)
DECLARE @b NUMERIC(19,8)

SET @var = -413.72063274
SET @a   = 651.00000000
SET @b   = 1000.00000000

DECLARE @v SQL_VARIANT 
SET @v =  CAST(@a/@b AS NUMERIC(24,10))* CAST(@var AS NUMERIC(23,8))

SELECT    CAST(SQL_VARIANT_PROPERTY(@v, 'BaseType') AS VARCHAR(30)) AS BaseType,    
          CAST(SQL_VARIANT_PROPERTY(@v, 'Precision') AS INT) AS PRECISION,    
          CAST(SQL_VARIANT_PROPERTY(@v, 'Scale') AS INT) AS Scale