# Dynamic decimal precision

• stelianx

SSChasing Mays

Points: 604

I need the precision of a decimal value to be dynamically controlled. Example:

DECLARE @input as decimal(16,6)

SET @input = 1.234567

DECLARE @precision INT

SET @precision = 2

-- SELECT CAST(@input AS DECIMAL(16, @precision)) <-- This does not work
-- SELECT CAST(@input AS DECIMAL(16, 2)) <-- This works This way, If @precision = 2
1.23 should be returned
If @precision = 3
1.234 should be returned

Is this possible?

• Kishore.P

SSCrazy Eights

Points: 8147

stelianx (5/6/2009)

I need the precision of a decimal value to be dynamically controlled. Example:

DECLARE @input as decimal(16,6)

SET @input = 1.234567

DECLARE @precision INT

SET @precision = 2

-- SELECT CAST(@input AS DECIMAL(16, @precision)) <-- This does not work

-- SELECT CAST(@input AS DECIMAL(16, 2)) <-- This works

This way,

If @precision = 2

1.23 should be returned

If @precision = 3

1.234 should be returned

Is this possible?

try the following way:

``` declare @input decimal (16, 6) , @precision int , @output varchar (1000) select @input = 1.234567 , @precision = 4 select @output = 'select cast (' + convert (varchar (100), @input) + ' as decimal (16, ' + convert (varchar (100), @precision) + ')) ' print (@output) exec (@output) ```

• stelianx

SSChasing Mays

Points: 604

That worked, thanks.

Now I am wondering how this can be used in a view π

I guess it is not possible. I was thinking about creating an udf to format decimal, but it's semed impossible to me.

• arun.sas

SSChampion

Points: 11831

Hi Kishore,

When the value like 1.23999

Then itβs round the value

Select @input = 1.23999, @precision = 4

Result to 1.2400

For that better we use the direct round function?

Like select round(@input,@precision)

ARUN SAS

• stelianx

SSChasing Mays

Points: 604

Well, it is "almost" good π

PRINT round(1.23456, 2)

displays 1.230000, while I need 1.23

I know it is not a good behaviour to format from SQL, but I really have to.

• rgillings

SSCrazy

Points: 2114

If the value is for display purposes, and not required to be a decimal data type, you could format it as a string like below:

``` DECLARE @Input DECIMAL(16,6) DECLARE @precision INT SELECT @input = 1.23999, @precision = 4 SELECT CONVERT(VARCHAR, SUBSTRING(CONVERT(VARCHAR,round(@input,@precision)), 0, CHARINDEX('.', CONVERT(VARCHAR,@input),0) + @precision + 1)) ```

• stelianx

SSChasing Mays

Points: 604

Thanks!

This is what I needed.

• deepikachaudhary31

SSC Journeyman

Points: 85

Thanks this is exactly what i wanted:)

• divyaeaga

SSC Journeyman

Points: 91

HI..

How to do the dynamic precision from a table

for ex: I have a table Currency with Rate (float) and Precision.

Currency Rate precision

AUD 12.12 5

AED 123.123123 7

I want to get these values as

AUD 12.12000

AED 123.1231230

• brijeshbajpai

SSC Rookie

Points: 37

rgillings - Friday, May 8, 2009 8:48 AM

declare @precision int =2
declare @ddd decimal(16,6)=10.9555
select convert(float,round(@ddd,@precision))

Viewing 10 posts - 1 through 10 (of 10 total)