AS declare -- @TabNam varchar(64), --临时表名 @mtr int, --物料编号 @jqty float --上月结存 -- select @TabNam='#abc' -- exec('create table #abc(mtr int,skt int,mcs int,jqty float)') create table #abc(mtr int,skt int,mcs int,jqty float) declare CurMtr cursor read_only for select id from mtr where mcs=@mcs open CurMtr while @@fetch_status=0 begin fetch next from CurMtr into @mtr --上存结余 select @jqty=(select amt from act where id in(select max(id) from act where act.typ !=8 and act.del=0 and act.stt=1 and act.skt=@skt and datediff(mm,act.dat,@dat)=0 and act.mtr=@mtr)) if(@jqty is null) select @jqty=0 -- exec('select '+@mtr+','+@skt+','+@mcs+','+@jqty+' into '+@TabNam) insert #abc(mtr,skt,mcs,jqty) values(@mtr,@skt,@mcs,@jqty) end deallocate CurMtr -- exec ('select * from '+@TabNam) select * from #abc
改成##abc exec('select '+@mtr+','+@skt+','+@mcs+','+@jqty+' into '+@TabNam)
改成
exec('insert '+@TabNam+ 'select '+@mtr+','+@skt+','+@mcs+','+@jqty)
@mcs int, --物料类别
@skt int, --仓库编号
@dat datetime --报表日期
AS
declare
-- @TabNam varchar(64), --临时表名
@mtr int, --物料编号
@jqty float --上月结存
-- select @TabNam='#abc'
-- exec('create table #abc(mtr int,skt int,mcs int,jqty float)')
create table #abc(mtr int,skt int,mcs int,jqty float)
declare CurMtr cursor read_only for select id from mtr where mcs=@mcs
open CurMtr
while @@fetch_status=0
begin
fetch next from CurMtr into @mtr
--上存结余
select @jqty=(select amt from act where id in(select max(id) from act where act.typ !=8 and act.del=0 and act.stt=1 and act.skt=@skt and datediff(mm,act.dat,@dat)=0 and act.mtr=@mtr))
if(@jqty is null)
select @jqty=0
-- exec('select '+@mtr+','+@skt+','+@mcs+','+@jqty+' into '+@TabNam)
insert #abc(mtr,skt,mcs,jqty) values(@mtr,@skt,@mcs,@jqty) end
deallocate CurMtr
-- exec ('select * from '+@TabNam)
select * from #abc
用全局临时表不好,如果有两个进程同时执行此存储过程,就有问题了。
你说的对!