现在有个表
id formula(nvarchar)
1 300+200-50-(300*20%)
2 200*5%+100
如何把值计算出来，例如 300+200-50-(300*20%)＝490
有好的办法吗，急用
------解决方案--------------------
declare @t table(id int, formula nvarchar(40))
insert @t
select 1, N '300+200-50-(300*20%) ' union all
select 2, N '200*5%+100 '
declare @id int,@formula nvarchar(40),@value float,@sql nvarchar(100)
declare @tmp table(id int,value float) /*保存计算结果的临时表*/
----定义游标
declare cur CURSOR STATIC for select id,formula from @t
open cur
fetch next from cur into @id,@formula
while @@fetch_status = 0
begin
----执行公式
set @sql = N 'select @value = ' + replace(@formula, '% ', '/100 ')
EXEC sp_executesql @sql,N '@value float output ',@value OUTPUT
insert @tmp select @id,@value
fetch next from cur into @id,@formula
end
close cur
deallocate cur
----查看计算结果
select * from @tmp
/*结果
id value
----------- -----------------------------------------------------
1 390.0
2 110.0
*/
------解决方案--------------------
create table # (id int, formula nvarchar(300))
insert into #
select 1 , '300+200-50-(300*20%) ' union all
select 2 , '200*5%+100 '
declare @sql varchar(2000)
set @sql= ' '
select @[email protected]+ 'select '+cast(id as varchar)+ ',(select '+formula+ ') union all ' from #
set @sql=replace(left(@sql,len(@sql)-len( 'union all ')), '% ', '/100 ')
print @sql
exec(@sql)