封装游标存储过程(Pr_execsql2)

领测软件测试网
使用脚本自己可以执行自己的特性,封装游标操作存储过程如下:
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

文章来源于领测软件测试网 https://www.ltesting.net/