EXECUTE 后的事务计数指出缺少了 COMMIT 或 ROLLBACK TRANSACTION 语句。

shilifeng 2009-05-05 03:54:13



/*-----------------------------------------------------------------------------------------------------------

功能：提交表单并自动转交到下一步（适用于固定流程）

时间：2008.10.11

参数：

说明：

节点模式（0-任意授权者完成，1-所有授权者处理，2-自由节点）

步骤人员办理模式（0-会签，1-非会签）



--------------------------------------------------------------------------------------------------------------*/

CREATE      proc Arch_GotoNextStep

@ArchObjectID int, 

@StepOrder int,

@GroupIndex int,

@UserID nvarchar(50),

@ConsignedBy nvarchar(50),

@SignText nvarchar(200),



@sqlUpdateFile nvarchar(4000),

@sqlInsertFile nvarchar(4000),

@sqlInsertStep nvarchar(4000),

@Sys int,

@Mobile int



AS 



 SET XACT_ABORT ON 

 begin tran t1



-------------------------当前流程属性-----------------------------

 declare @ArchClassID int          --流程定义编号

 declare @ArchFounder nvarchar(50) --流程创建人编号

-------------------------当前步骤属性-----------------------------

 declare @StepIndex int       --流程步骤定义序号

 declare @StepModelID int  --节点模式

 declare @CurrMode int       --步骤人员办理模式



-------------------------下一步骤属性-----------------------------

 declare @NextStepModelID int  --步节模式

 declare @NextStepType int     --节点定义类型

 declare @MobileNumber varchar(30)--发送人手机号码



 --判断是否为本步骤处理人

 if NOT  exists(select * from [ArchStepObjPersion] 

      WHERE ArchObjectID=@ArchObjectID and StepOrder=@StepOrder 

	and Transctor=@UserID and Transctor = @UserID and Consign = @ConsignedBy and GroupIndex = @GroupIndex and State<1)

 RETURN



---------------------------------------初始化起始值-----------------------------

--取出当前流程定义编号

 SELECT @ArchClassID= ArchClassID FROM [ArchObject] WHERE ArchObjectID= @ArchObjectID 



--取出当前步骤定义的索引和人员办理模式

 SELECT @StepIndex=StepIndex,@CurrMode = State From  [ArchStepObject] where ArchObjectID= @ArchObjectID and StepOrder=@StepOrder

 SELECT @StepModelID=StepModelID FROM [ArchStepClass] where ArchClassID=@ArchClassID AND  StepIndex=@StepIndex



 ----------------------------修改当前步骤信息及状态---------------------------------------------------------



 --删除处理人的委托或者被委托人员



 IF @ConsignedBy <> '' 

 begin

     Delete From ArchStepObjPersion Where ArchObjectID =@ArchObjectID and State <= 0 and StepOrder =@StepOrder 

           and  Transctor =@ConsignedBy and GroupIndex = @GroupIndex

 end

 ELSE

 begin

     Delete From ArchStepObjPersion Where ArchObjectID =@ArchObjectID and State <= 0 and StepOrder =@StepOrder  

           and   Consign =@UserID  and GroupIndex = @GroupIndex

 end

 -- 修改当前步骤信息（办理意见、意见内容）

 Update ArchStepObjPersion Set TransResult = 1 ,TransSign = @SignText,State = 1,endtime = getdate()

     WHERE ArchObjectID =@ArchObjectID  and StepOrder = @StepOrder and Transctor = @UserID AND Consign = @ConsignedBy and GroupIndex = @GroupIndex



 ----------------------------处理公共附件及当前办理人附件-----------------------------

 if @sqlUpdateFile<>'' exec(@sqlUpdateFile)--更新公共附件信息

 if @sqlInsertFile<>'' exec(@sqlInsertFile)--添加公共附件信息

 if @sqlInsertStep<>'' exec(@sqlInsertStep)--添加当前办理人附件信息



 ----------------------------当前步骤处理--------------------------------------------------

   IF ((@StepModelID =1) or (@StepModelID = 2 and @CurrMode = 1))--//当前步骤是会签

   BEGIN

      IF EXISTS(Select * From ArchStepObjPersion Where ArchObjectID =@ArchObjectID  and  State <= 0  and StepOrder =@StepOrder)

      BEGIN

        return

      END

   END

   Else

   begin

     Delete From ArchStepObjPersion Where ArchObjectID =@ArchObjectID and StepOrder =@StepOrder And (Transctor <> @UserID or Consign <> @ConsignedBy) and GroupIndex = @GroupIndex

   end



  ----------------------------判断当前步骤是否为最后一步-----------------------------

  IF NOT EXISTS(Select * From ArchStepClass Where ArchClassID =@ArchClassID  and StepIndex > @StepIndex)

  BEGIN    

    -- 结束公文流	

    --Update ArchStepObject Set State = 1 Where ArchObjectID =@ArchObjectID and StepOrder=@StepOrder

    if not Exists(Select * From ArchStepObjPersion Where ArchObjectID =@ArchObjectID  and State < 1)

          Update ArchObject Set State = 1 Where ArchObjectID =@ArchObjectID

    return

  END



  ----------------------------下一步处理-----------------------------  

  --下一步定义类型	

  SELECT @NextStepType = StepTypeID FROM [ArchStepClass] where ArchClassID=@ArchClassID AND  StepIndex=@StepIndex + 1



  Insert into ArchStepObject(ArchObjectID,StepIndex,StepOrder,BeginTime,State,Remark) 

                           values (@ArchObjectID,@StepIndex + 1,@StepOrder+1,getdate(),0,'')



  --下一步骤为返回发起人节点

  if (@NextStepType = 6)

  begin

     --查询出流程创建人编号

     select @ArchFounder = Founder From ArchObject Where ArchObjectID = @ArchObjectID

     

     insert into ArchStepObjPersion(ArchObjectID,StepOrder,Transctor,GroupIndex,BeginTime,EndTime,TransResult,TransSign,State)

        values(@ArchObjectID,@StepOrder + 1,@ArchFounder,1,getdate(),'',0,'',-1)

  end

  else

  begin

    --创建下一步人员

       declare @NextIndex int 

       set @NextIndex = @StepIndex+1

       if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[temp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

        DROP table temp

        create table temp(UID NVARCHAR(50))

        INSERT INTO TEMP EXEC FLOW_GetStepUser  @ArchClassID,@NextIndex

        insert into ArchStepObjPersion(ArchObjectID,StepOrder,GroupIndex,BeginTime,EndTime,TransResult,TransSign,State,Transctor) 

                    SELECT @ArchObjectID,@StepOrder+1,1,getdate(),'',0,'',-1,Transctor 

		   FROM(SELECT [Uid] as Transctor FROM [temp]) as b  

	     	if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[temp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

        drop table temp    

  end  

commit tran t1

SET XACT_ABORT OFF