Im working on a problem involving partitioned sql multiplications using fairly high precision.
A recent mistake prompted me to do some research, and I came across a humiliating article ( http://msdn.microsoft.com/en-us/library/ms190476.aspx ) on how accuracy and decimal result scale are calculated.
In my understanding, in order to avoid truncation or unexpected rounding, sometimes it is necessary to give operands and, possibly, intermediate calculations to sizes that will work, and give the desired results. Each operand is assigned to me as a decimal (19.5). There is nothing I can do about it, since we support such a wide range of values.
My question is . Can I reduce / reduce the type of these decimal (19.5) values on the fly to the smallest decimal type (precision and scale) that will hold the value?
For example, can I apply this variable (@Cw) to decimal (4.1) “on the fly” before it is used in my multiplication?
DECLARE @Cw decimal(19,5)
SET @Cw = 113.5
-–should show 4 instead of 19
SELECT SQL_VARIANT_PROPERTY(@NewCw, 'Precision')
–-should show 1 instead of 5
SELECT SQL_VARIANT_PROPERTY(@NewCw, 'Scale')
Thank!
source
share