sql server 存储过程

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

 

sql server 存储过程

标签：