Dynamically generate decimal points

I have a requirement where I need to compare decimal points based on a column given in a table. I understand it 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 below, we get the error

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

      

I tried below, it works, but wanted to know if there is any feature or any other possibility in sql server 2008 R2 that will directly select the column value and select 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)

      

+3


source to share


1 answer


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

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

      



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

+5


source







All Articles