CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Azar
Azar

Reputation: 1867

Dynamically generate the decimal points

I have requirement where I need to compare the decimal points based on column provided in the table. I understand this needs to be done dynamically but is there any other way to achieve this ?

create table #temp(Value1 decimal(18,10), Value2 decimal(18,12), DPtoCompare int)

insert into #temp 
select 123.45478888, 123.4578888, 3 union 
select 23.45478888, 23.4547988, 4 union 
select 456.454789, 456.45786, 5 union 
select 88.2356789, 88.2356787, 6

If we try the below we get error

select * from #temp 
        where convert(decimal(18,DPtoCompare) , Value1) = convert(decimal(18,DPtoCompare) , Value2)

I tried the below, it works but wanted to know if there is any function or any other possibility in sql server 2008 R2 which will directly pick the column value and pick the decimal points.

        declare @sql varchar(max)
        select @sql = isnull(@sql,'')+'
            select Value1, Value2 from #temp 
            where DPtoCompare = '+CONVERT(varchar, t.DPtoCompare)
            +' and convert(decimal(18,'+CONVERT(varchar,t.DPtoCompare)+'), value1) != '
            +'convert(decimal(18,'+CONVERT(varchar,t.DPtoCompare)+'), value2)'
            from (select distinct DPtoCompare from #temp) t

        exec(@sql)

Upvotes: 3

Views: 945

Answers (1)

Marc Guillot
Marc Guillot

Reputation: 6455

The round function allows you to pass a field to set the number of decimal places.

select * 
from #temp 
where round(Value1, DPtoCompare, 1) = round(Value2, DPtoCompare, 1)

Set the third parameter of Round to 1 if you want to truncate to n decimals your values instead of rounding them.

Upvotes: 5

Related Questions