如何在嵌套存储过程中实现事务。

[xiaoyehua]

有2个存储过程P1,P2.
P1用来做进行逻辑判断，然后决定执行一次或多次P2。
P2嵌套在P1中，P2只有一条update的SQL。

现在有一种情况就是如果需要执行比如3次P2。那么我希望这3次执行过程中，如果有一个P2执行失败，则全部回滚。
我想到了用事务，粗略SQL如下

CREATE PROCEDURE P1
--定义P1参数
...
AS
--P1的其他判断SQL
.....

BEGIN TRAN
P2(@P2参数1）；
P2(@P2参数2）；
P2(@P2参数3）；
COMMIT TRAN

我想请教下大伙，我这样写能达到效果嘛？或者我P2中需要怎么写才能保证P2他自己不会自动提交呢？请大神写出点关键SQL就可以了。
谢谢！

[Mr_Nice]

SET XACT_ABORT ON 先填上 使用 try catch 参考 http://msdn.microsoft.com/zh-cn/library/ms175976.aspx 最后，真的不建议这样写事务 ，之前碰到-1 +1 事务@@不同的问题。 真是不好处理 。 参考 @@transcount

[xiaoyehua]

那大神建议怎么做呢？

[xiaoyehua]

谢谢大神，我已经想到另外一个方法了，就是P2只是返回SQL字符串而已，原来exec（@sql）支持 多个sql的字符串的执行的。

[专注or全面]

其实楼主关注的问题可以归结为事务嵌套问题 只要做好异常处理，对于事务嵌套事务的问题，内层事务回滚，外层事务也跟着回滚，外层事务回滚，内层事务就没有执行的机会 也就不用说回滚了 纯手工写了一个测试的例子，看效果就行了

create table t1(id int,name varchar(10))

create table t2(id int,name varchar(10))

create proc p_innerProcTest
as
begin

begin try
	begin tran
		insert into t1 values(1,'A');
		if exists(select 1 from t1 where id=1)
		begin tran
			insert into t1 values(1,'ABCDEFGHIJKLMN');--执行失败，
		commit
	commit
end try

begin catch
	rollback;
end catch
end


EXEC p_innerProcTest

SELECT * FROM t1

select * from t2

[xiaoyehua]

感谢LS的例子，我感觉LS例子中事务的控制其实已经在同一个“页面”上，所以控制和流程是比较明确的。对于存储过程，不知道是否实用（我感觉嵌套的存储过程，事务不在同一个“页面”上，不方便控制）。

[专注or全面]

其实我偷了个懒，直接在事务中又开了一个内层事务 意识是为了表达事务嵌套的异常的处理方式 我明白你的意思是存储过程调用存储过程，其实处理起来大同小异 你可以参考下面文章中总结的 http://www.cnblogs.com/rush/archive/2011/12/11/2284262.html ********************************************************************** 另外是题外话，可以无视 对事事务的处理，老老实实在脚本中做异常处理 有人喜欢使用SET XACT_ABORT ON声明的方式，从而忽略了异常处理，也可以达到事务的目的，但是不建议这么做 One way to make your error handling simpler is to run with SET XACT_ABORT ON. With this setting, most errors abort the batch. This may give you the idea that you don't need any error handling at all in your stored procedures, but not so fast! I said most errors, not all errors. Even if XACT_ABORT is ON, as a minimum you must check for errors when calling stored procedures, and when you invoke dynamic SQL. This is because XACT_ABORT does not affect compilation errors, and compilation errors are typically those that cause SQL Server to abandon execution of a procedure and return control to the caller. Nor will the batch be aborted because of a RAISERROR, so if you detect an error condition, you still need to return a non-zero value to the caller, that has to check for it. Also, when XACT_ABORT is ON, error 266, Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing, does not abort the batch. This is not documented in Books Online, and it makes me a little nervous that there might be more errors that SET XACT_ABORT ON does not affect. In any case, I would suggest that if you use SET XACT_ABORT ON, you should use it consistently, preferably submitting the command from the client directly on connection. What you should not do, is to use it sometimes and sometimes not. Particularly it is bad, if you as an individual programmer as your private standard insert a SET XACT_ABORT ON in the procedures you write, while your colleagues do not. Say that another programmer calls your code. He might have some error-handling code where he logs the error in a table. While SQL Server may abort the batch for some errors, sufficiently many errors let execution continue to make such a scheme worthwhile. As long as not any joker starts to play games with SET XACT_ABORT ON, that is. (Note: there are some situations with distributed queries where SET XACT_ABORT ON is required for them to work. This is the exception to the rule that you should not use XACT_ABORT ON sometimes.)

[xiaoyehua]

非常感谢！我觉得问题很好解决了！