create table #t(code varchar(10),p1 varchar(20),p2 varchar(20),p3 varchar(20))insert into #t select 'aaa','dw-dh','dw*dh','dw-1'insert into #t select 'bbb','(dw+dh)*2','dw-2','dw+1'insert into #t select 'ccc','dw-dh-2','dw*dh+1','dw-6'create table #t_d(code varchar(10),dw varchar(10),dh varchar(10),p1 varchar(20),p2 varchar(20),p3 varchar(20))insert into #t_d select 'aaa','45','4','','',''insert into #t_d select 'bbb','3','55','','',''insert into #t_d select 'ccc','41','23','','','' create proc test@ss varchar(100) ,@z decimal(18,2) output as declare @sql nvarchar(1000) set @sql= 'select @re= '+@ssexec sp_executesql @sql,N'@re decimal(18,2) output ',@z output go 上面这个存储过程单独的示例用法如下declare @aa decimal(18,2)exec m_compute '343*34',@aa outputprint @aacreate proc p_compute asdeclare @p1 varchar(20)declare @code varchar(20)declare @aa decimal(18,2)declare @i intdeclare @col varchar(10)declare @sql varchar(800)declare @upsql varchar(800)declare @cursql varchar(1000)declare @curupsql varchar(100)set @i = 1while @i < 4 begin set @col = 'p'+cast(@i as varchar(10)) set @upsql = 'update #t_d set '+@col+' =replace(replace(t.'+@col+',''dw'',d.dw),''dh'',d.dh) from #t t,#t_d d where t.code = d.code' exec(@upsql)-- set @cols = @cols + @col-- set @sql = 'select code,'+ @cols + ', from #t'-- print @sql set @cursql = 'declare c1 cursor forselect code,'+@col+' from #t_d'exec(@cursql)open c1fetch next from c1 into @code,@p1while @@fetch_status = 0 beginexec test @p1,@aa outputprint @aaset @curupsql = 'update #t_d set '+@col+ '='+ cast(@aa as varchar(20))+' where code = '''+@code+''''exec(@curupsql)--update #t_d set p1 = @aa where code = @codefetch next from c1 into @code,@p1endclose c1deallocate c1set @i = @i+1end
