Is there a way to set a default precision for decimal calculations in SQL Server 2005?

Question

I am multiplying the values of a few decimal columns together in SQL Server 2005 in a stored procedure. The SP already has tens of calculations.

The problem is that there's precision being lost. Instead of the result of the calculation having precision of the largest precision involved in the calculation, it's 6 decimal points. I don't know if that's a SQL Server default. None of the columns has 6 decimal points.

Arbitrary example of problem:

column1(10,10) * column2 (8,8) * column3(8,5) results in say decimal(10,6).

I need the precision to be 10 instead of 6.

I know I can cast all the columns and calculations to what I want but that's too much work for a bunch of complicated stored procedures.

Is there a general default I can force in the SP?

Something like 'SET DECIMALPRECISION 10'? (I made that up)

EDIT: It's scale instead of precision

Answer 1

No, here is how SQL Server determines the precison: http://msdn.microsoft.com/en-us/library/ms190476.aspx

EDIT Just to be sure, you can see exactly what precision and scale SQL Server is using for your expression by using this:

SELECT SQL_VARIANT_PROPERTY( YOUR_EXPRESSION_HERE  , 'Precision')
SELECT SQL_VARIANT_PROPERTY( YOUR_EXPRESSION_HERE  , 'Scale')

SELECT SQL_VARIANT_PROPERTY( column1(10,10) * column2 (8,8) * column3(8,5)  , 'Scale')

I'm not so sure your precision/scale is what you think... try this:

declare @one decimal(10,10)
declare @two decimal(8,8)
declare @three decimal(8,5)
set @one=.1234567891
set @two=.12345678
set @three=123.12345
select @one*@two*@three as Value
    ,SQL_VARIANT_PROPERTY(@one*@two*@three, 'Precision') AS 'Precision'
    ,SQL_VARIANT_PROPERTY(@one*@two*@three, 'Scale') AS 'Scale'

output:

Value                                   Precision   Scale
--------------------------------------- ----------- -----
1.87659562388635548234810               28          23

(1 row(s) affected)

chart at the link I provided says that the scale would be 10+8+5=23, and this shows that it is 23 as well. As a result, you can round down to 10 if you'd like.

Answer 2

Just to be clear, are you wanting the precision to be 10 or the scale to be 10? The format of the type declaration is:

DECIMAL(precision, scale)

The link KM provided shows some examples of each. If you wanted to keep from writing the same code over and over, I would either write a function to call that would provide all the rounding and math you'd repeat over and over, or create a user defined type that does it for you (either with T-SQL or via the CLR).