--Each numeric data type of distinct precision & scale is considered a 
-- different data type. Hence result of an operation involving these will vary.
declare @one numeric(10,2) declare @zero numeric(10,2) 
select @one = -1.0 
select @zero = 0.0 
select @one * @zero, convert(numeric(10, 2), @one * @zero)
select @one * @zero as n into #t
select * from #t
exec ('use tempdb exec sp_help ''#t''')

/* Multiplication operation & precision / scale calculation
@one (p1, s1)
@zero (p2, s2)
p1 +  p2 + 1, s1 + s2
21, 4
*/

/*
 If you execute the code, you will see that the CAST prints the value correctly.
 I think it has got something to with the precision & scale of the resulting
 data when you multiply two values. I will explain.
 Consider two numeric values n1(a, x) and n2(b, y)
 where a & b is the precision and x & y is the scale.
 If you multiply n1*n2, then the resulting value will be n3(a + b + 1, x + y).
 In your example, the value will be n3(21, 4) after the multiplication operation.
 You can notice the scale from the number of zeroes after the  decimal point.
 This will explain why you are getting -.0000 instead of .00.
 The CAST converts it to the data type numeric(10, 2) which prints .00.
 To test the actual precision & scale of the converted value, add this line to the code
*/
-- SELECT @one*@zero as n INTO #t
-- Exec('use tempdb exec sp_help #t')

/*
 From the sp_help output you can see that the numeric column is indeed defined as numeric(21, 4).
 Another point to note is that each numeric data type of distinct precision & scale is
 considered a different data type.
 Hope this info provides some insight.
*/

This page was last updated on May 01, 2006 04:28 PM.