假设我在sql server 2008中有以下sql语句：
BEGIN TRANSACTION sqlStatement1 EXEC sp1 sqlStatement3 COMMIT TRANSACTION
sp1的代码
BEGIN TRANSACTION sqlStatement2 ROLLBACK TRANSACTION
我的问题是：sqlStatement3是否实际执行？
解决方法
您可以使用
transaction savepoints. sp1可以使用像
Error Handling and Nested Transactions中描述的模式：
create procedure [usp_my_procedure_name]
as
begin
set nocount on;
declare @trancount int;
set @trancount = @@trancount;
begin try
if @trancount = 0
begin transaction
else
save transaction usp_my_procedure_name;
-- Do the actual work here
lbexit:
if @trancount = 0
commit;
end try
begin catch
declare @error int,@message varchar(4000),@xstate int;
select @error = ERROR_NUMBER(),@message = ERROR_MESSAGE(),@xstate = XACT_STATE();
if @xstate = -1
rollback;
if @xstate = 1 and @trancount = 0
rollback
if @xstate = 1 and @trancount > 0
rollback transaction usp_my_procedure_name;
raiserror ('usp_my_procedure_name: %d: %s',16,1,@error,@message) ;
end catch
end
这样的模式允许在sp1中完成的工作回滚,但是保持包含的事务处于活动状态.
