1 USE [NyghSql] 2 GO 3 /****** Object: StoredProcedure [dbo].[delete_date] Script Date: 10/13/2015 17:30:06 ******/ 4 SET ANSI_NULLS ON 5 GO 6 SET QUOTED_IDENTIFIER ON 7 GO 8 ALTER procedure [dbo].[delete_date] 9 ( 10 @applycode nvarchar(100) 11 ) 12 as 13 --声明变量 定义变量用逗号隔开 14 declare @applycode_var nvarchar(100), 15 @instanceid_var nvarchar(100), 16 @t_ywsloid_var nvarchar(100), 17 @areaPart_oid nvarchar(100), 18 @areaPartoid nvarchar(100) 19 begin 20 --查出 工作流实例 21 select @instanceid_var=Instanceid from t_ywsl where applycode=@applycode 22 --删除 工作流实例对应的任务表（待办和已办） 23 delete from AssignTask where AssignTasK.Instanceid=@instanceid_var 24 --查询业务受理oid 25 select @t_ywsloid_var=t_ywsloid from t_ywsl where applycode=@applycode 26 27 --循环删除对应的坐标范围 28 --sql server 29 --定义游标 30 declare my_cursor cursor 31 --读取数据放到游标中 32 for(select AreaPartoid from AreaPart where T_YWSLOID=@t_ywsloid_var) 33 --打开游标 34 open my_cursor 35 --读取集合中的第一行数据 36 fetch next from my_cursor into @areaPart_oid 37 --全局变量,0表示fetch语句成功；-1表示语句失败或此行不在结果集中；-2被提取的行不存在 38 while @@FETCH_STATUS=0 39 begin 40 delete from AreaPoints where AreaPartOID=@areaPart_oid 41 --读取下一行 42 fetch next from my_cursor into @areaPart_oid 43 end 44 close my_cursor 45 deallocate my_cursor 46 47 --删除对应的地块列表 48 delete from AreaPart where T_YWSLOID=@t_ywsloid_var 49 --删除对应的附件管理 50 delete from FileManage where T_YWSL_FK=@t_ywsloid_var 51 --审批意见 52 delete from SPYJ where T_YWSL_FK=@t_ywsloid_var 53 --流程意见 54 --工作流实例记录本 55 delete from FlowInstance where Instanceid=@instanceid_var; 56 --活动转移条件集 57 declare t_cursor cursor for 58 select TaskTicketoid from TaskTicket where Instanceid=@instanceid_var 59 open t_cursor 60 fetch next from t_cursor into @areaPartoid 61 while @@FETCH_STATUS=0 62 begin 63 delete from TransConditionEx where TaskTicket_FK=@areaPartoid 64 fetch next from t_cursor into @areaPartoid 65 end 66 close t_cursor 67 deallocate t_cursor 68 --任务表 69 delete from TaskTicket where Instanceid=@instanceid_var 70 --业务受理表 71 delete from t_ywsl where applycode=@applycode 72 end