I hope everyone might have faced this problem.
For example:
declare @a numeric(18,2)
declare @b numeric(18,2)
set @a = 10.35
set @b = 20.34
In the above declared variables it is mentioned that precision = 18 and scale = 2, so I am expecting the result of same format. But here SQL server adds more scale while multiplying or dividing.
select @a*@b
Ans: 210.5190
select @a/@b
Ans: 0.50884955
But these were correct results and the scale value is decided based on the below formula.
Here let’s consider @a has p1 as precision and s1 as scale and @b p2 as precision and s2 as scale
Here p1 and p2 = 18 and both s1 and s2 = 2.
So the precision value would be of
p1 - s1 + s2 + max(6, s1 + p2 + 1) = 18 – 2 + 2 + max(6, 2 + 18 + 1) = 18 + 21 = 39
and scale value would be of
max(6, s1 + p2 + 1) = max(6, 2 + 18 + 1) = 21.
So to avoid more scale value we need to use cast or convert in the result
select cast(@a*@b as numeric(18,2))
Ans: 210.52
select cast(@a/@b as numeric(18,2))
Ans: 0.51
