想设计一个存储过程,该过程能实现一下功能:
1、传入一个带分隔符的字符串
2、分隔开的各个字符串都是一条SQL执行语句
3、该存储过程依次执行字符串中的各条SQL语句
4、执行过程中,出任何问题,该SQL语句的执行全部回滚
1、传入一个带分隔符的字符串
2、分隔开的各个字符串都是一条SQL执行语句
3、该存储过程依次执行字符串中的各条SQL语句
4、执行过程中,出任何问题,该SQL语句的执行全部回滚
go
create procedure executestr
@sqlstr nvarchar(1000)
as
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
EXEC(@sqlstr)
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
END CATCH;
END
go
exec executestr 'insert into tb select 1; insert into tb select 2;'
select * from tb
delete from tb
/*
(1 行受影响)(1 行受影响)
id
-----------
1
2(2 行受影响)(2 行受影响)
*/
go
exec executestr 'insert into tb select 1; insert into tb select ''A'' ;'
select * from tb
/*
(1 行受影响)
id
-----------(0 行受影响)*/go
drop table tb
drop procedure executestr
@sql NVARCHAR(4000),
@splitchar VARCHAR(1)
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
DECLARE @sqlTemp NVARCHAR(4000)
SET @sqlTemp = REPLACE(@sql, @splitchar, ';')
--PRINT @sqlTemp
EXEC(@sqlTemp)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
END