批量更新数据表内科学计数法（varchar列）为数值数据

create procedure DataFormat
 @tablename varchar(100)
as
begin
declare @Columns table
(
   [name] varchar(100)
)
declare @sql varchar (max)
declare @CurrentColumnName varchar(100)
set @CurrentColumnName=null
insert into @Columns select b.name  from sysobjects a inner join syscolumns b on a.id=b.id where a.name=@tablename and a.xtype='U' and b.xtype='167'

declare MyCursor Cursor for select [name] from @Columns for read only
open MyCursor
fetch next from MyCursor into @CurrentColumnName
while @@fetch_status = 0
begin
   set @sql='update '+ @tablename +' set '+@CurrentColumnName+' = cast(
   cast(substring('+@CurrentColumnName+',1,CHARINDEX(''e'','+@CurrentColumnName+')-1) as decimal(30,10))*
   power(cast(10 as decimal(30,10)),cast(substring('+@CurrentColumnName+',CHARINDEX(''e'','+@CurrentColumnName+')+1,len('+@CurrentColumnName+')-CHARINDEX(''e'','+@CurrentColumnName+')) as decimal))
      
   as decimal(30,2)) where '+@CurrentColumnName+ ' like ''%e%'''
 print(@sql)
 exec(@sql)
 if @@error<>0
 begin
  close MyCursor
  deallocate MyCursor
  return
 end
 fetch next from MyCursor into @CurrentColumnName    
end
close MyCursor
deallocate MyCursor

end
--思路：查找操作表的varchar列，通过游标批量更新其中为科学计数法的数据，检索匹配【%e%】的数值为操作数据

--bug：对于正常varchar列且含有e的数据，会出错。