首页 > 代码库 > 动态游标存储过程 表名为参数

动态游标存储过程 表名为参数

 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

 

动态游标存储过程 表名为参数