ccoa 删除proc

ALTER proc [dbo].[P_DeleteFlowByStarter]
@empNo nvarchar(10),
@fk_flow nvarchar(10),
@OID INT
AS
BEGIN
 declare @msg nvarchar(100)
 --  if not exists(select * from   WF_GenerWorkFlow where WorkID=@OID and FK_Flow=@fk_flow andStarter=@empNo)
 --set @msg=N'您不是流程发起人，不能删除此流程！'
 --else
  begin
  declare @NDTable nvarchar(100)
  declare @DtlTable nvarchar(100)
  declare @MyRptTable nvarchar(100)
  declare @RptTable nvarchar(100),@TrackTable nvarchar(100)
  declare @PTable nvarchar(100)
  declare @sql nvarchar(3000)
  
  DECLARE ND_cursor CURSOR FOR
  select 'ND'+CAST(NodeID AS NVARCHAR(10)) from WF_Node where FK_Flow=@fk_flow
  OPEN ND_cursor
  FETCH NEXT FROM ND_cursor into @NDTable
  WHILE @@FETCH_STATUS = 0
  BEGIN 
   ---删除Dtl
   DECLARE Dtl_cursor CURSOR FOR
   select PTable from Sys_MapDtl where FK_MapData=@NDTable
   OPEN Dtl_cursor
   FETCH NEXT FROM Dtl_cursor into @DtlTable
   WHILE @@FETCH_STATUS = 0
   BEGIN
    set @sql='delete from '+@DtlTable+' WHERERefPK=@OID'
    EXEC SP_EXECUTESQL  @sql,N'@OID INT',@OID
   FETCH NEXT FROM Dtl_cursor into @DtlTable 
   END
   CLOSE Dtl_cursor
   DEALLOCATE Dtl_cursor
   ----
  set @sql='delete from '+@NDTable+' whereOID=@OID'  
  EXEC SP_EXECUTESQL  @sql,N'@OID INT',@OID
  FETCH NEXT FROM ND_cursor into  @NDTable
  END
  CLOSE ND_cursor
  DEALLOCATE ND_cursor
  
  set @MyRptTable = 'ND'+CAST(CAST(@fk_flow AS int) as nvarchar(10))+'MyRpt'
  set @RptTable='ND'+CAST(CAST(@fk_flow AS int) as nvarchar(10))+'Rpt'
  set @TrackTable='ND'+CAST(CAST(@fk_flow AS int) as nvarchar(10))+'Track'
  
  set @sql='select @PTable=PTable from  Sys_MapData  where No=@MyRptTable'  
  EXEC SP_EXECUTESQL  @sql,N'@PTable nvarchar(100) out,@MyRptTable nvarchar(100)',@PTable out,@MyRptTable
  
  --删除记录
  set @sql='INSERT INTO WF_WorkFlowDeleteLog(Fk_FLOW,Oper,DeleteDT,OID,FID,FK_Dept,Title,FlowStarter,FlowStartRDT,WFState,FK_NY,FlowEnderRDT,FlowEndNode,FlowDaySpan,MyNum,FlowEmps)
   select @fk_flow Fk_FLOW, ''admin'' Oper,GETDATE() DeleteDT,OID,FID,FK_Dept,Title,FlowStarter,FlowStartRDT,WFState,FK_NY,FlowEnderRDT,FlowEndNode,FlowDaySpan,MyNum,FlowEmps
   from '+@PTable+' where OID=@OID'
  EXEC SP_EXECUTESQL  @sql,N'@fk_flow nvarchar(10),@OID INT',@fk_flow,@OID 
  
  set @sql='delete from '+@PTable+' where (OID=@OID orFID=@OID) AND FID<>0'  
  EXEC SP_EXECUTESQL  @sql,N'@OID INT',@OID
  
  set @sql='select @PTable=PTable from  Sys_MapData  where No=@RptTable'  
  EXEC SP_EXECUTESQL  @sql,N'@PTable nvarchar(100) out,@RptTable nvarchar(100)',@PTable out,@RptTable
  set @sql='delete from '+@PTable+' whereOID=@OID'  
  EXEC SP_EXECUTESQL  @sql,N'@OID INT',@OID
  
  set @sql='delete from '+@TrackTable+' where  @OID IN(WorkID,FID)'  
  EXEC SP_EXECUTESQL  @sql,N'@OID INT',@OID
  
  DELETE FROM WF_GenerFH WHERE  FID=@OID ANDFK_Flow=@fk_flow
  delete  from   WF_GenerWorkFlow where (WorkID=@OID orFID=@OID) and FK_Flow=@fk_flow --and Starter=@empNo
  delete  from   WF_GenerWorkerlist where(WorkID=@OID orFID=@OID) and FK_Flow=@fk_flow
  DELETE FROM WF_ReturnWork WHERE WorkID=@OID
  DELETE FROM WF_CCList WHERE WorkID=@OID
  DELETE FROM WF_CHEval WHERE WorkID=@OID
  
  set @msg=N'删除成功！'
 end
 select @msg
END

 

======判断人员是否在某个部门下面====

alter function [dbo].[f_IsInDept](
@empNo nvarchar(10),@DeptNo nvarchar(10))
RETURNS int
as
begin
 DECLARE @bol int,@dep nvarchar(10)
 set @bol=0
 select @dep=FK_Dept from Port_Emp where No=@empNo
 
 while(0=@bol)
 begin
  if (@DeptNo=@dep)
  begin
   set @bol=1
   break
  end  
  else
  begin
   if 100=@dep
    break
   else   
    select @dep=ParentNo from Port_Dept where No=@dep    
  end
 end
 return @bol
end