两个存储过程间事务的问题，错误信息“不能在 INSERT-EXEC 语句内部使用 ROLLBACK 语句”，请高手帮忙，谢谢。

[zorroonline]

两个存储过程test1和test2，在TEST1的事务里面调用TEST2（EXEC TEST2）,TEST2里面也有事务，当TEST2里面的事务执行ROLLBACK时，就会报错“消息 3915，级别 16，状态 0，过程 IMS_TJZZJYJEQ1，第 100 行，不能在 INSERT-EXEC 语句内部使用 ROLLBACK 语句。”,原因是运行到存储过程TEST2时，有两个事务，当执行ROLLBACK时就会出错，因为TEST2是通过EXEC的方式执行的。

[wzy_love_sly]

ROLLBACK   TRANSACTION
将显式事务或隐性事务回滚到事务的起点或事务内的某个保存点。

语法
ROLLBACK   [   TRAN   [   SACTION   ]
        [   transaction_name   ¦   @tran_name_variable
        ¦   savepoint_name   ¦   @savepoint_variable   ]   ]

参数
transaction_name

是给   BEGIN   TRANSACTION   上的事务指派的名称。transaction_name   必须符合标识符规则，但只使用事务名称的前   32   个字符。嵌套事务时，transaction_name   必须是来自最远的   BEGIN   TRANSACTION   语句的名称。

@tran_name_variable

是用户定义的、含有有效事务名称的变量的名称。必须用   char、varchar、nchar   或   nvarchar   数据类型声明该变量。

savepoint_name

是来自   SAVE   TRANSACTION   语句的   savepoint_name。savepoint_name   必须符合标识符规则。当条件回滚只影响事务的一部分时使用   savepoint_name。

@savepoint_variable

是用户定义的、含有有效保存点名称的变量的名称。必须用   char、varchar、nchar   或   nvarchar   数据类型声明该变量。

注释
ROLLBACK   TRANSACTION   清除自事务的起点或到某个保存点所做的所有数据修改。ROLLBACK   还释放由事务控制的资源。

不带   savepoint_name   和   transaction_name   的   ROLLBACK   TRANSACTION   回滚到事务的起点。嵌套事务时，该语句将所有内层事务回滚到最远的   BEGIN   TRANSACTION   语句。在这两种情况下，ROLLBACK   TRANSACTION   均将   @@TRANCOUNT   系统函数减为   0。ROLLBACK   TRANSACTION   savepoint_name   不减少   @@TRANCOUNT。

ROLLBACK   TRANSACTION   语句若指定   savepoint_name   则不释放任何锁。

在由   BEGIN   DISTRIBUTED   TRANSACTION   显式启动或从本地事务升级而来的分布式事务中，ROLLBACK   TRANSACTION   不能引用   savepoint_name。

在执行   COMMIT   TRANSACTION   语句后不能回滚事务。

在事务内允许有重复的保存点名称，但   ROLLBACK   TRANSACTION   若使用重复的保存点名称，则只回滚到最近的使用该保存点名称的   SAVE   TRANSACTION。

在存储过程中，不带   savepoint_name   和   transaction_name   的   ROLLBACK   TRANSACTION   语句将所有语句回滚到最远的   BEGIN   TRANSACTION。在存储过程中，ROLLBACK   TRANSACTION   语句使   @@TRANCOUNT   在触发器完成时的值不同于调用该存储过程时的   @@TRANCOUNT   值，并且生成一个信息。该信息不影响后面的处理。

如果在触发器中发出   ROLLBACK   TRANSACTION：  

将回滚对当前事务中的那一点所做的所有数据修改，包括触发器所做的修改。


触发器继续执行   ROLLBACK   语句之后的所有其余语句。如果这些语句中的任意语句修改数据，则不回滚这些修改。执行其余的语句不会激发嵌套触发器。


在批处理中，不执行所有位于激发触发器的语句之后的语句。  
每次进入触发器，@@TRANCOUNT   就增加   1，即使在自动提交模式下也是如此。（系统将触发器视作隐性嵌套事务。）

在存储过程中，ROLLBACK   TRANSACTION   语句不影响调用该过程的批处理中的后续语句；将执行批处理中的后续语句。在触发器中，ROLLBACK   TRANSACTION   语句终止含有激发触发器的语句的批处理；不执行批处理中的后续语句。

ROLLBACK   TRANSACTION   语句不生成显示给用户的信息。如果在存储过程或触发器中需要警告，请使用   RAISERROR   或   PRINT   语句。RAISERROR   是用于指出错误的首选语句。

ROLLBACK   对游标的影响由下面三个规则定义：  

当   CURSOR_CLOSE_ON_COMMIT   设置为   ON   时，ROLLBACK   关闭但不释放所有打开的游标。


当   CURSOR_CLOSE_ON_COMMIT   设置为   OFF   时，ROLLBACK   不影响任何打开的同步   STATIC   或   INSENSITIVE   游标，也不影响已完全填充的异步   STATIC   游标。将关闭但不释放任何其它类型的打开的游标。


对于导致终止批处理并生成内部回滚的错误，将释放在含有该错误语句的批处理内声明的所有游标。不论游标的类型或   CURSOR_CLOSE_ON_COMMIT   的设置，所有游标均将被释放，其中包括在该错误批处理所调用的存储过程内声明的游标。在该错误批处理之前的批处理内声明的游标以规则   1   和   2   为准。死锁错误就属于这类错误。在触发器中发出的   ROLLBACK   语句也自动生成这类错误。  
权限
ROLLBACK   TRANSACTION   权限默认授予任何有效用户。 

[pt1314917]

帮顶。。

[wzy_love_sly]

给每个事务加个事务名行不行，没试过

[zorroonline]

加事务名也不行，试了，谢谢大家的回复，这个问题好几天都没有找到答案

[wzy_love_sly]

我去测试下

[zorroonline]

大家没有遇到这样的问题吗

[zorroonline]

谢谢wzy_love_sly 了

[zjcxc]

CREATE PROC p1
AS
	-- 初始化事务和返回值设置
	DECLARE
		@Trancount int,
		@re int
	SELECT
		@re = 0,
		@Trancount = @@ROWCOUNT
	
	-- 如果外层(调用者)无事务, 则直接开启事务, 否则保存事务点(这样后面可以根据情况做事务处理)
	IF @Trancount = 0
		BEGIN TRAN
	ELSE
		SAVE TRAN TRAN_Save_Point

	---.... 你的处理
	-- 在可能出错的语句后面应该包括类似这样的错误处理语句
	IF @@ERROR <> 0
		GOTO lb_Error


-- 成功提交事务
lb_Succeed:	
	IF @Trancount = 0
		COMMIT TRAN
	GOTO lb_Return

-- 失败回滚事务
lb_Error:
	IF @Trancount = 0
		ROLLBACK TRAN
	ELSE
		ROLLBACK TRAN TRAN_Save_Point
	SET @re = -1

-- 退出处理
lb_Return:
	RETURN @re

[zjcxc]

按上面的模板调整你的存储过程就行了, 不管是被调用的(子存储过程), 还是调用的(外层调用者), 都可以使用上述模板

[zorroonline]

谢谢邹建老师，我现在就去试

[dawugui]

老大出马,我接个分.帮顶.

[zorroonline]

不好意思，邹建老师，还是会报这个错误：消息 3915，级别 16，状态 0，过程 IMS_TJZZJYJEQ1，第 102 行
不能在 INSERT-EXEC 语句内部使用 ROLLBACK 语句。

我把我的存储过程代码贴出来吧

[wzy_love_sly]

我也抱错，还不光这个 ......

[wzy_love_sly]

create table b( j int)
insert into b select 2

create proc proc_b
as
begin
BEGIN TRANSACTION tranb
begin
insert into b select 3
select 1/0
end
if @@Error<>0
Rollback transaction tranb
else
commit transaction tranb
end

exec proc_b
select * from b

单用可以，套proc抱错........

[wzy_love_sly]

create table a( i int)
insert into a select 1

create table b( j int)
insert into b select 2



create proc proc_a
as
begin
exec proc_b
if @@error<>0
rollback
end

create proc proc_b
as
begin
BEGIN TRANSACTION 
begin
insert into b select 3
select 1/0
end
if @@Error<>0
Rollback transaction 
else
commit transaction 
end

exec proc_a

select * from b

2

a不加事务可以

[zorroonline]

存储过程proc1调用存储过程proc2

存储过程 proc1
ALTER PROCEDURE [dbo].[proc1]
-- Add the parameters for the stored procedure here
AS
BEGIN

SET NOCOUNT ON;

CREATE TABLE #T1(
ID VARCHAR(10)
)

BEGIN TRAN

INSERT INTO #T1(ID) EXEC PROC2 'PARA1' --调用存储过程proc2

IF @@ERROR<>0
BEGIN
ROLLBACK TRAN
RETURN
END

COMMIT TRAN
END

存储过程proc2
ALTER PROCEDURE [dbo].[proc2]
-- Add the parameters for the stored procedure here
@PARA VARCHAR(10)
AS
BEGIN
SET NOCOUNT ON;

CREATE TABLE #T2(
IDVALUE VARCHAR(10) NULL
)

CREATE TABLE #T3(
IDTEXT VARCHAR(10) NULL
)

BEGIN TRAN

INSERT INTO #T2(IDVALUE)
VALUES (@PARA)

IF @@ERROR<>0
BEGIN
ROLLBACK TRAN
RETURN
END

IF NOT EXISTS (SELECT 1 FROM #T3) --在此执行rollback
BEGIN
ROLLBACK TRAN --这句报错
RETURN
END

COMMIT TRAN

END

[zorroonline]

就是用exec执行的存储过程里，如果执行到rollback就会报错。
错误信息就是： 不能在 INSERT-EXEC 语句内部使用 ROLLBACK 语句。

[zjcxc]

不能在 INSERT-EXEC 语句内部使用 ROLLBACK 语句。


这个错误提示已经够清楚了

[prcgolf]

uppuu