封装游标存储过程(Pr_execsql2)-数据库专栏,SQL Server

使用脚本自己可以执行自己的特性,封装游标操作存储过程如下:
create procedure pr_execsql2
       @asqlstring varchar(8000) –输入的sql语句
as
exec (
–启动事务
begin tran

declare @asql varchar(8000)
declare tnames_cursor cursor local fast_forward for +  @asqlstring +
open tnames_cursor
fetch next from tnames_cursor into @asql
while (@@fetch_status=0)
begin
print @asql
exec (@asql)
if @@error <> 0 goto finalexit
fetch next from tnames_cursor into @asql
end
close tnames_cursor
deallocate tnames_cursor

commit tran
return

finalexit:
rollback tran
close tnames_cursor
deallocate tnames_cursor

)
使用方法:
declare @sql varchar(8000)
set @sql=select update a set a.a= + b.a from b 
exec pr_execsql2 @sql