临时表代替游标
废话不说,直接先上代码: ----不使用游标,使用临时表循环:更新没有配置作废章的流程 DECLARE @temp TABLE ( requestid INT, zno NVARCHAR(10) ); INSERT INTO @temp(requestid,zno) SELECT requestid,zno FROM formtable_main_278 where zfz is null DECLARE @rid AS INT, @zno AS NVARCHAR(50) WHILE EXISTS(select requestid from @temp) BEGIN select top 1 @rid = requestid from @temp print '@rid is:' +cast(@rid as varchar(100)) exec zpro_d203_back_action @rid DELETE FROM @temp WHERE requestid=@rid; end
USE [XQPMU_ALL] GO /****** Object: StoredProcedure [dbo].[pFrwBussinessType] Script Date: 10/25/2018 16:06:41 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /***************************** 过程名称:pFrwBussinessType 目的: 获取所有流程各公司的业务类别 调用者: 工作流引擎 创建日期:2005-05-18 作 者:王世英 修改日期: 修改人: 修改内容: 输入参数:cCoID char(6),cModuleID varchar(3),cTypeName varchar(200) 输出参数:数据集 重要提示:存储过程调用 涉及表: #tTmpBussinessType,#tTmpBussiness 讨论: *****************************/ ALTER PROC [dbo].[pFrwBussinessType] @cCoID char(6), @cModuleID varchar(3), @cTypeName varchar(200) AS declare @iTypeID int --类别标识 declare @cName varchar(200) --流程名称 declare @cProc varchar(200) --存储过程名称 create table #tTmpBussinessType(cName varchar(200)) create table #tTmpReturn(iTypeID int,cName varchar(200)) if @cTypeName is not null and @cTypeName <> '' begin declare cProc cursor for select a.iProcessTypeID,b.cProcessTypeName,a.cBusinessTypeProc from tFrwProcessType a Left Join tFrwProcessCAndE b On b.cProcessTypeName = a.cProcessTypeName where b.cProcessTypeNameE = rtrim(@cTypeName) open cProc fetch next from cProc into @iTypeID,@cName,@cProc while(@@fetch_status=0) begin if @cProc is not null and @cProc <> '' begin insert into #tTmpBussinessType exec @cProc @cCoID insert into #tTmpReturn select @iTypeID,@cName+'-'+cName from #tTmpBussinessType end else begin insert into #tTmpReturn select @iTypeID,@cName end delete #tTmpBussinessType fetch next from cProc into @iTypeID,@cName,@cProc end close cProc deallocate cProc end else begin declare cProc cursor for select a.iProcessTypeID,c.cProcessTypeName,a.cBusinessTypeProc from tFrwProcessType a join tFrrUnit b on a.cMenuID = b.cUnitID Left Join tFrwProcessCAndE c On c.cProcessTypeName = a.cProcessTypeName where b.cModuleID like rtrim(@cModuleID)+'%' open cProc fetch next from cProc into @iTypeID,@cName,@cProc while(@@fetch_status=0) begin if @cProc is not null and @cProc <> '' begin insert into #tTmpBussinessType exec @cProc @cCoID --if(@@error<>0) --begin --raiserror ('运行类别存储过程 %s 出错',16,1,@cProc) --end insert into #tTmpReturn select @iTypeID,@cName+'-'+cName from #tTmpBussinessType end else begin insert into #tTmpReturn select @iTypeID,@cName end delete #tTmpBussinessType fetch next from cProc into @iTypeID,@cName,@cProc end close cProc deallocate cProc end select * from #tTmpReturn return --[dbo].[pFrwBussinessType]
if exists (select * from sysobjects where id = object_id(N'[dbo].[pFrwCopyWorkFlow]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop proc [dbo].[pFrwCopyWorkFlow] SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO /************************************************************************************************************* 过程名称: [pFrwCopyWorkFlow] 目 的: 调 用 者: 创建日期: 作 者: 杨林 修改日期: 修改内容: 输入参数: 输出参数: 重要提示: 涉 及 表: 1、 讨 论: 使用样例:exec dbo.pFrwCopyWorkFlow 150, '000001', 31, 'testName', '00000001' *************************************************************************************************************/ create proc dbo.pFrwCopyWorkFlow ( @iProcessID int, @cCoID char(6), @iProcessTypeID int, @cProcessName varchar(50), @cOperator char(8) ) as create table #tFrwProcess_temp ( iProcessID int identity, iProcessTypeID int null, cCoID char(6) not null, cProcessName varchar(50) not null, gMapXML image null, cMemo varchar(2000) not null, dCreate datetime not null default getDate(), cOperator char(10) not null, iValidState tinyint null, cConfirmor char(8) null ) create table #tFrwProcessNodes_temp ( iNodeID int not null, iProcessID int not null, iNodeType tinyint not null default 1, cNodeName varchar(200) not null, bFirstNode int not null default 0, iYesNodeID int not null default -1, iNoNodeID int not null default -2, iSignRule tinyint not null default 1, iAutoTime int not null default 0 ) create table #tFrwProcessActor_temp ( iID int identity, iNodeID int not null, iType tinyint not null default 0, cHumanID char(8) null, iPositionID int null, iRelation tinyint null, iRelativeID int null, iConditionID int null, cOperator varchar(2) null, nValue numeric(20,2) null ) declare @baseNodeID int --变量 存储当前节点最大值 declare @d_value int --差值 declare @iProcessID_new int --存储新工作流标识, 这是系统产生的递增量 begin transaction --新节点标识的起始值 select @baseNodeID = MAX(iNodeID)+1 from tFrwProcessNodes --复制工作流 insert into #tFrwProcess_temp (iProcessTypeID, cCoID, cProcessName, gMapXML, cMemo, dCreate, cOperator, iValidState, cConfirmor) select @iProcessTypeID, @cCoID, @cProcessName,gMapXML,cMemo, GETDATE(), @cOperator, 0, null from tFrwProcess where iProcessID = @iProcessID --复制节点 insert into #tFrwProcessNodes_temp (iNodeID, iProcessID, iNodeType, cNodeName, bFirstNode, iYesNodeID, iNoNodeID, iSignRule, iAutoTime) select iNodeID, iProcessID, iNodeType, cNodeName, bFirstNode, iYesNodeID, iNoNodeID, iSignRule, iAutoTime from tFrwProcessNodes where iProcessID = @iProcessID order by iNodeID asc --获取最大值与当前最小值 的差值 select top 1 @d_value = @baseNodeID - iNodeID from #tFrwProcessNodes_temp order by iNodeID --复制参与人 insert into #tFrwProcessActor_temp (iNodeID, iType, cHumanID, iPositionID, iRelation, iRelativeID, cOperator, nValue) select iNodeID+@d_value, iType, cHumanID, iPositionID, iRelation, iRelativeID+@d_value, cOperator, nValue from tFrwProcessActor where iNodeID in ( select iNodeID from #tFrwProcessNodes_temp where iNodeType <> 2 ) --过滤掉条件节点 --将标识更新为新的标识,增量为差值 update #tFrwProcessNodes_temp set iNodeID = iNodeID + @d_value --更新同意与不同意跳转节点 update #tFrwProcessNodes_temp set iYesNodeID = iYesNodeID + @d_value where iYesNodeID > 0 update #tFrwProcessNodes_temp set iNoNodeID = iNoNodeID + @d_value where iNoNodeID > 0 insert into tFrwProcess (iProcessTypeID, cCoID, cProcessName, gMapXML, cMemo, dCreate, cOperator, iValidState, cConfirmor) select iProcessTypeID, cCoID, cProcessName, gMapXML, cMemo, dCreate, cOperator, iValidState, cConfirmor from #tFrwProcess_temp if @@error <> 0 Goto PROBLEM select @iProcessID_new = SCOPE_IDENTITY() insert into tFrwProcessNodes (iNodeID, iProcessID, iNodeType, cNodeName, bFirstNode, iYesNodeID, iNoNodeID, iSignRule, iAutoTime) select iNodeID, @iProcessID_new, iNodeType, cNodeName, bFirstNode, iYesNodeID, iNoNodeID, iSignRule, iAutoTime from #tFrwProcessNodes_temp if @@error <> 0 Goto PROBLEM insert into tFrwProcessActor (iNodeID, iType, cHumanID, iPositionID, iRelation, iRelativeID, iConditionID, cOperator, nValue) select iNodeID, iType, cHumanID, iPositionID, iRelation, iRelativeID, iConditionID, cOperator, nValue from #tFrwProcessActor_temp if @@error <> 0 Goto PROBLEM --调试信息 update #tFrwProcessNodes_temp set iProcessID = @iProcessID_new select * from #tFrwProcess_temp select * from #tFrwProcessNodes_temp select * from #tFrwProcessActor_temp rollback transaction goto exitSection PROBLEM: ROLLBACK TRANSACTION exitSection: drop table #tFrwProcess_temp drop table #tFrwProcessNodes_temp drop table #tFrwProcessActor_temp GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
if exists (select * from sysobjects where id = object_id(N'[dbo].[pFrwChnageActor]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop proc [dbo].[pFrwChnageActor] SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO /************************************************************************************************************* 过程名称: [pFrwChnageActor] 目 的: 调 用 者: 创建日期: 作 者: 修改日期: 修改内容: 输入参数: 输出参数: 重要提示: 涉 及 表: 1、 讨 论: 使用样例:exec dbo.pFrwChnageActor *************************************************************************************************************/ create proc dbo.pFrwChnageActor ( @oldcHumanID char(8), @newcHumanID char(8), @cOperator char(8) ) as begin transaction update tFrwProcessActor set cHumanID = @newcHumanID where cHumanID = @oldcHumanID if @@error <> 0 Goto PROBLEM update tFrwProcessInstanceDetail set cOperatorID = @newcHumanID WHERE cOperatorID = @oldcHumanID and iOperate = 0 if @@error <> 0 Goto PROBLEM insert into tFrwProcessHumanChange (cChangeFront, cChangeAfter, cOperator, dCreate) values(@oldcHumanID, @newcHumanID, @cOperator, GETDATE()) if @@error <> 0 Goto PROBLEM commit transaction goto exitSection PROBLEM: ROLLBACK TRANSACTION exitSection: GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
南来地,北往的,上班的,下岗的,走过路过不要错过!
======================个性签名=====================
之前认为Apple 的iOS 设计的要比 Android 稳定,我错了吗?
下载的许多客户端程序/游戏程序,经常会Crash,是程序写的不好(内存泄漏?刚启动也会吗?)还是iOS本身的不稳定!!!
如果在Android手机中可以简单联接到ddms,就可以查看系统log,很容易看到程序为什么出错,在iPhone中如何得知呢?试试Organizer吧,分析一下Device logs,也许有用.