A
alexhans
Unregistered / Unconfirmed
GUEST, unregistred user!
/*事由:我在做一个C/S的系统,想优化一下性能及解决多用户的冲突*/
/* 在客户端使用内存临时表进行数据编辑及输入*/
/* 1-提交时,通过客户端命令创建2个唯一性的数据库永久表,退出时删除*/
/* 2-@TmpZb varchar(20)-临时性主表,@TmpMx varchar(20)-临时性明细表 */
/* 3-编写存储过程hp_SaveAJCD来处理,将@TmpZb的一条记录存入进仓主表TWLJC中*/
/* 4-将@TmpMx的多条记录存入进仓明细表TWLJCmx中 */
/*问题:在存储过程中使用了多个带 #的临时表,必须使用sp_sqlexec来执行动态SQL命令*/
/* 通过分析,存储过程在并没有按我的设想执行完成,请大家帮我分析一下*/
CREATE PROCEDURE hp_SaveAJCD @TmpZb varchar(20), @TmpMx varchar(20),
@JCID int, @NewCKID int, @OldCKID int
as
DECLARE @newJCID int /*新的进仓ID,必须保存后由系统自动生成*/
Select @newJCID=@JCID /*将原进仓ID赋值给@newJCID*/
/*创建与主表相同结构的临时表*/
Create Table #TmpJC(JCID int,JDate DateTime,JCDH varchar(14),CKID int,
BMID int,EmID int,Note varchar(100),TypeID int,Oper varchar(10),
BType varchar(4),bmPath varchar(20) )
DECLARE @SQL varchar(1000)
IF @JCID<>0 /*@JCID>0,说明此操作是修改单据*/
Begin
/*客户端已通过命令将一条主表记录存于@TmpZb 表中*/
Select @SQL='Insert Into #TmpJC(JCID,JDate,JCDH ,CKID, BMID ,EmID ,Note , TypeID,Oper , Btype ,bmPath ) '
+' Select JCID,JDate,JCDH ,CKID, BMID ,EmID ,Note , TypeID,Oper , Btype ,bmPath From '+@TmpZb
exec sp_sqlexec @SQL /*将表@TmpZb中的记录复制到##TmpJC中*/
exec hp_DelAJCD @JCID,@oldCKID, 1 /*---1-保留主表记录 2-删除明细*/
/*调用另一过程修改库存并将旧的明细项删除*/
Update TWLJC set JCDH=T.JCDH,JCDate=T.JCDate, CKID=T.CKID, BMID=T.BMID,
EmID=T.EmID, Note=T.Note, TypeID=T.TypeID, Oper=T.Oper, Btype=T.Btype, bmPath=T.bmPath
From TWLJC J, #TmpJC T where J. JCID=@JCID
/*修改主表*/
End
else Begin
/*新单:增加一条主表记录*/
Select @SQL='Insert Into TWLJC(JDate,JCDH ,CKID, BMID ,EmID ,Note , TypeID,Oper , Btype ,bmPath ) '
+' Select JDate,JCDH ,CKID, BMID ,EmID ,Note , TypeID,Oper , Btype ,bmPath From '+@TmpZb
exec sp_sqlexec @SQL
Select @newJCID=SCOPE_IDENTITY() /*取得新的主表ID*/
exec hp_WriteToDebug ' *新单' ,'@error','@newJCID',@@error,@newJCID
End
/*增加库存*/
Create Table #TmpSum (RID int Identity ,CKID int,WLID int,WLDJ int,FzQu int)
/*临时表#TmpSum用于存放合并后的明细,客户端允许重复输入同种物料的明细*/
Create Table #TmpJcMx (JCmxID int,JCID int,WLID int,WLBH varchar(30),WLDJ int,FzQu int,JCQu Float)
/*便于操作创建临时表#TmpJcMx*/
select @SQL='Insert Into #TmpJcMx Select * From '+@TmpMx
exec sp_sqlexec @SQL /*将表@TmpMx的数据转移到#TmpJcMx中*/
exec hp_WriteToDebug ' Insert Into #TmpJcMx' ,'@error','@@Rowcount',@@error,@@Rowcount
Select @SQL=' Insert Into #TmpSum (WLID,WLDJ,FzQu) '
+' select WLID,WLDJ, sum(FzQu) as FzQu '
+' From #TmpJcMx Group by WLID,WLDJ having Sum(Fzqu)<>0 '
/* 将明细记录按WLID(物料ID)和WLDJ(物料等级)合并后放在#TmpSum中*/
exec sp_sqlexec @SQL
exec hp_WriteToDebug ' Insert Into #TmpSum' ,'@error','@@Rowcount',@@error,@@Rowcount
/*很奇怪在这里:按以上写法可执行,而如下写法却没反应
Insert Into #TmpSum (WLID,WLDJ,FzQu)
select WLID,WLDJ, sum(FzQu) as FzQu
From #TmpJcMx Group by WLID,WLDJ having Sum(Fzqu)<>0
exec hp_WriteToDebug ' Insert Into #TmpSum' ,'@error','@@Rowcount',@@error,@@Rowcount
*/
/*通过下面的语句,测出程序到这里已经停止*/
if exists (select * from sysobjects
where id = object_id(N'JCTmp1') and OBJECTPROPERTY(id, 'IsUserTable')= 1)
drop table JCTmp1
select * into JCtmp1 from #TmpSum
/*以上句子只为测试使用*/
exec hp_WriteToDebug '@newCKID' ,'@error','@newCKID',@@error,@newCKID
Update #TmpSum set CKID=@NewCKID /*不执行*/
Insert Into TWLJCMx(JCID,WLID,WLBH,WLDJ,FzQu,Jcqu)
select @NewJCID,WLID,wLBH,WLDJ,FzQu,JCQu from #TmpJcMx
exec hp_WriteToDebug 'Insert Into TWLJCMx' ,'@error','@@Rowcount',@@error,@@Rowcount
Update TWlKC set FzQu=K.FzQu+T.FzQu
From TWLKC k Left Join #TmpJcMxSum T on k.CKID=@NewCKID and k.WLID=T.WLID and k.WLDJ=T.WLDJ
Where T.FzQu<>0
exec hp_WriteToDebug ' Update TWlKC ' ,'22','33',1,@@Rowcount
Insert into TWlKC (CKID,WLID,WLDJ,FzQu)
select @newCKID,WLID,WLDJ,FzQu From #TmpJcMxSum
where WLID not in
(select t.WLID from #TmpJcMxSum T left join TWLKC k on k.WLID=T.WLID and k.wLDJ=t.WLDj and k.CKID=@newCKID)
GO
/* 在客户端使用内存临时表进行数据编辑及输入*/
/* 1-提交时,通过客户端命令创建2个唯一性的数据库永久表,退出时删除*/
/* 2-@TmpZb varchar(20)-临时性主表,@TmpMx varchar(20)-临时性明细表 */
/* 3-编写存储过程hp_SaveAJCD来处理,将@TmpZb的一条记录存入进仓主表TWLJC中*/
/* 4-将@TmpMx的多条记录存入进仓明细表TWLJCmx中 */
/*问题:在存储过程中使用了多个带 #的临时表,必须使用sp_sqlexec来执行动态SQL命令*/
/* 通过分析,存储过程在并没有按我的设想执行完成,请大家帮我分析一下*/
CREATE PROCEDURE hp_SaveAJCD @TmpZb varchar(20), @TmpMx varchar(20),
@JCID int, @NewCKID int, @OldCKID int
as
DECLARE @newJCID int /*新的进仓ID,必须保存后由系统自动生成*/
Select @newJCID=@JCID /*将原进仓ID赋值给@newJCID*/
/*创建与主表相同结构的临时表*/
Create Table #TmpJC(JCID int,JDate DateTime,JCDH varchar(14),CKID int,
BMID int,EmID int,Note varchar(100),TypeID int,Oper varchar(10),
BType varchar(4),bmPath varchar(20) )
DECLARE @SQL varchar(1000)
IF @JCID<>0 /*@JCID>0,说明此操作是修改单据*/
Begin
/*客户端已通过命令将一条主表记录存于@TmpZb 表中*/
Select @SQL='Insert Into #TmpJC(JCID,JDate,JCDH ,CKID, BMID ,EmID ,Note , TypeID,Oper , Btype ,bmPath ) '
+' Select JCID,JDate,JCDH ,CKID, BMID ,EmID ,Note , TypeID,Oper , Btype ,bmPath From '+@TmpZb
exec sp_sqlexec @SQL /*将表@TmpZb中的记录复制到##TmpJC中*/
exec hp_DelAJCD @JCID,@oldCKID, 1 /*---1-保留主表记录 2-删除明细*/
/*调用另一过程修改库存并将旧的明细项删除*/
Update TWLJC set JCDH=T.JCDH,JCDate=T.JCDate, CKID=T.CKID, BMID=T.BMID,
EmID=T.EmID, Note=T.Note, TypeID=T.TypeID, Oper=T.Oper, Btype=T.Btype, bmPath=T.bmPath
From TWLJC J, #TmpJC T where J. JCID=@JCID
/*修改主表*/
End
else Begin
/*新单:增加一条主表记录*/
Select @SQL='Insert Into TWLJC(JDate,JCDH ,CKID, BMID ,EmID ,Note , TypeID,Oper , Btype ,bmPath ) '
+' Select JDate,JCDH ,CKID, BMID ,EmID ,Note , TypeID,Oper , Btype ,bmPath From '+@TmpZb
exec sp_sqlexec @SQL
Select @newJCID=SCOPE_IDENTITY() /*取得新的主表ID*/
exec hp_WriteToDebug ' *新单' ,'@error','@newJCID',@@error,@newJCID
End
/*增加库存*/
Create Table #TmpSum (RID int Identity ,CKID int,WLID int,WLDJ int,FzQu int)
/*临时表#TmpSum用于存放合并后的明细,客户端允许重复输入同种物料的明细*/
Create Table #TmpJcMx (JCmxID int,JCID int,WLID int,WLBH varchar(30),WLDJ int,FzQu int,JCQu Float)
/*便于操作创建临时表#TmpJcMx*/
select @SQL='Insert Into #TmpJcMx Select * From '+@TmpMx
exec sp_sqlexec @SQL /*将表@TmpMx的数据转移到#TmpJcMx中*/
exec hp_WriteToDebug ' Insert Into #TmpJcMx' ,'@error','@@Rowcount',@@error,@@Rowcount
Select @SQL=' Insert Into #TmpSum (WLID,WLDJ,FzQu) '
+' select WLID,WLDJ, sum(FzQu) as FzQu '
+' From #TmpJcMx Group by WLID,WLDJ having Sum(Fzqu)<>0 '
/* 将明细记录按WLID(物料ID)和WLDJ(物料等级)合并后放在#TmpSum中*/
exec sp_sqlexec @SQL
exec hp_WriteToDebug ' Insert Into #TmpSum' ,'@error','@@Rowcount',@@error,@@Rowcount
/*很奇怪在这里:按以上写法可执行,而如下写法却没反应
Insert Into #TmpSum (WLID,WLDJ,FzQu)
select WLID,WLDJ, sum(FzQu) as FzQu
From #TmpJcMx Group by WLID,WLDJ having Sum(Fzqu)<>0
exec hp_WriteToDebug ' Insert Into #TmpSum' ,'@error','@@Rowcount',@@error,@@Rowcount
*/
/*通过下面的语句,测出程序到这里已经停止*/
if exists (select * from sysobjects
where id = object_id(N'JCTmp1') and OBJECTPROPERTY(id, 'IsUserTable')= 1)
drop table JCTmp1
select * into JCtmp1 from #TmpSum
/*以上句子只为测试使用*/
exec hp_WriteToDebug '@newCKID' ,'@error','@newCKID',@@error,@newCKID
Update #TmpSum set CKID=@NewCKID /*不执行*/
Insert Into TWLJCMx(JCID,WLID,WLBH,WLDJ,FzQu,Jcqu)
select @NewJCID,WLID,wLBH,WLDJ,FzQu,JCQu from #TmpJcMx
exec hp_WriteToDebug 'Insert Into TWLJCMx' ,'@error','@@Rowcount',@@error,@@Rowcount
Update TWlKC set FzQu=K.FzQu+T.FzQu
From TWLKC k Left Join #TmpJcMxSum T on k.CKID=@NewCKID and k.WLID=T.WLID and k.WLDJ=T.WLDJ
Where T.FzQu<>0
exec hp_WriteToDebug ' Update TWlKC ' ,'22','33',1,@@Rowcount
Insert into TWlKC (CKID,WLID,WLDJ,FzQu)
select @newCKID,WLID,WLDJ,FzQu From #TmpJcMxSum
where WLID not in
(select t.WLID from #TmpJcMxSum T left join TWLKC k on k.WLID=T.WLID and k.wLDJ=t.WLDj and k.CKID=@newCKID)
GO