首页 > 代码库 > 动态游标存储过程 表名为参数
动态游标存储过程 表名为参数
1 Create procedure [dbo].[InsertOriginalCreateName_Vendor] 2 @TableName nvarchar(50) 3 as 4 Begin 5 declare @UserApplicant nvarchar(50); 6 declare @CreatedDate datetime; 7 declare @OriginalDate datetime; 8 declare @code nvarchar(50); 9 declare @action nvarchar(50);10 declare @id nvarchar(50);11 declare @sql nvarchar(max)12 set xact_abort on13 begin tran 14 set @sql=‘declare _cur cursor for select id,[External Partner Number] from ‘+@TableName15 exec (@sql)16 open _cur17 fetch next from _cur into @id,@code18 print @id19 while (@@fetch_status=0)20 begin21 begin try 22 if exists(select * from vendorheaders where code=@code)23 begin 24 if exists(select * from vendoritems where vendorheaderid in(select id from vendorheaders where code=@code) and RoleActionId=2)25 begin26 select top 1 @CreatedDate=CreatedDate,@UserApplicant=UserApplicant,@action=RoleActionId from vendoritems where vendorheaderid in(select id from vendorheaders where code=@code) and RoleActionId=2 order by CreatedDate asc 27 28 set @sql=‘update ‘+@TableName+‘ set [date]=‘+‘‘‘‘+convert(VARCHAR(20),@CreatedDate,120)+‘‘‘‘+‘,[Project manager]=‘+‘‘‘‘+@UserApplicant+‘‘‘‘+‘ where id=‘+@id29 exec (@sql)30 end31 else if exists(select * from vendoritems where vendorheaderid in(select id from vendorheaders where code=@code) and RoleActionId=4)32 begin 33 select top 1 @CreatedDate=CreatedDate,@UserApplicant=UserApplicant,@action=RoleActionId from vendoritems where vendorheaderid in(select id from vendorheaders where code=@code) and RoleActionId=4 order by CreatedDate asc 34 35 set @sql=‘update ‘+@TableName+‘ set [date]=‘+‘‘‘‘+convert(VARCHAR(20),@CreatedDate,120)+‘‘‘‘+‘,[Project manager]=‘+‘‘‘‘+@UserApplicant+‘‘‘‘+‘ where id=‘+@id36 exec (@sql)37 end38 39 else40 begin41 select top 1 @OriginalDate=CreatedDate from vendorheaders where code=@code order by CreatedDate asc42 set @sql=‘update ‘+@TableName+‘ set [date]=‘+‘‘‘‘+convert(VARCHAR(20),@OriginalDate,120)+‘‘‘‘+‘ where id =‘+@id43 exec (@sql)44 end45 end46 else 47 begin48 set @sql=‘update ‘+@TableName+‘ set [Project manager]=‘‘Not find this data‘‘ where id=‘+@id49 exec(@sql)50 end51 end try52 53 begin catch54 print ERROR_MESSAGE()55 rollback tran56 close _cur 57 deallocate _cur58 return59 end catch60 61 fetch next from _cur into @id,@code62 print @id63 end64 commit tran65 close _cur66 deallocate _cur67 End
动态游标存储过程 表名为参数
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。