27,579
社区成员
发帖
与我相关
我的任务
分享
create table ls(aa varchar(30))
insert into ls
select '3+(1*[4.000])' union all
select '4*[1.000]' union all
select '4*[100.000]'
-- 建存储过程
alter proc sp_ls(@f varchar(1),@i decimal(20,2))
as
begin
select aa,aa 'aa2',cast(0 as decimal(20,2)) 'x'
into #t
from ls
declare @aa varchar(20),@y decimal(20,2),@tsql nvarchar(4000)
declare ap scroll cursor for select aa from #t
open ap
fetch first from ap into @aa
while(@@fetch_status<>-1)
begin
select @tsql=N'select @y='+replace(replace(@aa,']',@f+rtrim(@i)+')'),'[','(');
exec master.dbo.sp_executesql @tsql,N'@y decimal(20,2) output',@y output;
update #t
set aa2=replace(replace(@aa,']',@f+rtrim(@i)+')'),'[','('),
x=@y
where aa=@aa and aa2=@aa;
fetch next from ap into @aa;
end
close ap
deallocate ap
select aa,aa2,x from #t
end
-- 测试1
exec sp_ls @f='+',@i=5
/*
aa aa2 x
------------------------------ ------------------------------ -----------
3+(1*[4.000]) 3+(1*(4.000+5.00)) 12.00
4*[1.000] 4*(1.000+5.00) 24.00
4*[100.000] 4*(100.000+5.00) 420.00
(3 row(s) affected)
*/
-- 测试2
exec sp_ls @f='*',@i=0.9
/*
aa aa2 x
------------------------------ ------------------------------ -----------
3+(1*[4.000]) 3+(1*(4.000*0.90)) 6.60
4*[1.000] 4*(1.000*0.90) 3.60
4*[100.000] 4*(100.000*0.90) 360.00
(3 row(s) affected)
*/