1. Registered User
Join Date
Feb 2009
Posts
51

A numeric value of Decimal(16,2) cannot be assigned to Decimal(16,4).

How to get a correct precision of a value (@V1) derived from two arbitrary decimal numbers (@X,@Y) with unknown precision and scale in to a decimal type variable.

declare @V1 as @decimal
set @V1 = @X*@Y

Will @V1 be having 4 decimals?

2. Registered User
Join Date
Apr 2012
Posts
213
I think that for 4 decimals should use the Round function. Ex:

Code:
`set @V1 = ROUND(@X * @Y, 4);`
Hope this helps.

3. Registered User
Join Date
Jan 2003
Location
Massachusetts
Posts
5,862
The variable or column that contains your result would have to be decimal (18, 4). The two numbers (Precision and scale) define 1 How many digits you allow, and 2 how many decimal places there are. Taking a smaller sample:
Code:
```create table #temp
(col1 decimal (5, 4))

insert into #temp values (10)```
fails miserably, because the datatype does not allow for a 10's column.

This, however does work:
Code:
```create table #temp
(col1 decimal (5, 4))

declare @in1 decimal (5, 2),
@in2 decimal (5, 2)

set @in1 = 1.00
set @in2 = 3.00

insert into #temp values (@in1/@in2)

select *
from #temp```

4. Registered User
Join Date
Feb 2009
Posts
51

## appreciate input so far

I think I ought to be clear a scalar function that return a decimal value and also round this decimal value accordingly by looking at another variable which defines number of decimals and so it will be:

1. FUNCTION X
2. RETURNS DECIMAL(30,8)

3. DECLARE @DECIMALS AS INT -- will have values 0 or 2 or 4 or 6
4. DECLARE @VALUE1 AS DECIMAL(18,6)
5. DECLARE @VALUE2 AS DECIMAL(18,2)
6. RETURN ROUND(@VALUE1* @VALUE2, @DECIMALS) --assume @decimals = 2

What will be the precision and scale of the constant as a result of multiplication?

What will be the return value precision and scale?

Will the above function work? if not how to fix it?

take a punt guys

side note:
What prevent MS SQL from making DECIMAL type similar to the ones that we used in programming languages?
Say we have a decimal variable created and assign a decimal value to it with a precision and a scale it will retain it.
But MS SQL doing this for constants.
So we can calculate and return decimals without knowing precision and scale.

Last edited by dbfHelp; 08-22-12 at 20:41.

5. Registered User
Join Date
Aug 2012
Posts
45
Originally Posted by dbfHelp
What will be the precision and scale of the constant as a result of multiplication?
Have you tried it yourself?
Code:
```DECLARE @Dec_18_2 DECIMAL(18,2),
@Dec_18_6 DECIMAL(18,6)

SELECT @Dec_18_2 = 10.12
SELECT @Dec_18_6 = 10.123456
SELECT @Dec_18_2
SELECT @Dec_18_6
SELECT @Dec_18_2 * @Dec_18_6```
What will be the return value precision and scale?
It looks like you are going to tell your function to return DECIMAL(30,8), so that is the precision and scale. If you are going to round anyway, then why not return DECIMAL(38,18)? This is what books online says...
Converting decimal and numeric Data
For the decimal and numeric data types, SQL Server considers each specific combination of precision and scale as a different data type. For example, decimal(5,5) and decimal(5,0) are considered different data types.

In Transact-SQL statements, a constant with a decimal point is automatically converted into a numeric data value, using the minimum precision and scale necessary. For example, the constant 12.345 is converted into a numeric value with a precision of 5 and a scale of 3.

Will the above function work? if not how to fix it?
Have you tried it as is? Does it do what you want it to do? You may want to be telling us if it works or not. Are you just looking for function syntax help?
Code:
```CREATE FUNCTION fnDecimalTest (@Value1 DECIMAL(18,2), @Value2 DECIMAL(18,6))
RETURNS DECIMAL(30,8)
AS
BEGIN
DECLARE @Return DECIMAL(30,8)
SELECT @Return = @Value1 * @Value2
RETURN @Return
END```
That is without rounding, so you can see what its returning

6. Registered User
Join Date
Feb 2009
Posts
51

sgnidow

still got to see what others have to say.

There is another question in my comment about DECIMAL variables in MS SQL Server. If you assign a numeric value with a decimal part to a variable of type DECIMAL(not given precision and scale) then decimal part will be dropped (i). but a decimal constant will retain the decimal part(ii). What reasons behind MS-SQL server that not retain the decimal part in (i) similar to in(ii)?

7. Registered User
Join Date
Aug 2012
Posts
45
Originally Posted by dbfHelp
If you assign a numeric value with a decimal part to a variable of type DECIMAL(not given precision and scale) then decimal part will be dropped (i).
Ok, I get this part, and this should demonstrate...
Code:
```DECLARE @Dec_no_precision DECIMAL
SELECT @Dec_no_precision = 10.123
SELECT @Dec_no_precision```
but a decimal constant will retain the decimal part(ii).
I'm not sure I get what you are asking here. By constant do you mean a value in a table that is defined as DECIMAL with no precision? If so, you will see the same behavior...

Code:
```IF OBJECT_ID('tempdb..#dec','u') IS NOT NULL
DROP TABLE #dec
CREATE TABLE #dec
(
dec_no_precision DECIMAL
)
INSERT INTO #dec VALUES
(10.123)

SELECT dec_no_precision
FROM #dec```
What reasons behind MS-SQL server that not retain the decimal part in (i) similar to in(ii)?
I can't answer that, but you could read every word in the DECIMAL and NUMERIC data type documentation in BOL, and you might find your answer. I do know one thing, and that is by explicitly declaring the scale and precision you don't have to worry about what SQL Server will do if you don't declare it. Could you give more detail about what you are trying to do? Is it that you don't like to see all the extra zero's at the end of your result?

Greg

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•