SqlServer存储过程中的嵌套事务

--父存储过程
CREATE PROCEDURE [dbo].[Test1]
  @Code         int
AS
BEGIN
    BEGIN TRANSACTION
    UPDATE [dbo].[TranTest] SET [id]='1', [value]=@Code WHERE ([id]='1');

    EXEC Test2 @Code
    --COMMIT TRANSACTION
    ROLLBACK TRANSACTION
	UPDATE [dbo].[TranTest] SET [id]='3', [value]=@Code WHERE ([id]='3');
END

--子存储过程
CREATE PROCEDURE [dbo].[Test2]
  @Code         int
AS
BEGIN
    BEGIN TRANSACTION
    UPDATE [dbo].[TranTest] SET [id]='2', [value]=@Code WHERE ([id]='2');
    COMMIT TRANSACTION
    --ROLLBACK TRANSACTION
END

--测试表和数据
CREATE TABLE [dbo].[TranTest] (
[id] int NOT NULL ,
[value] int NOT NULL ,
CONSTRAINT [PK_TranTest] PRIMARY KEY ([id])
)
ON [PRIMARY]
GO

INSERT INTO [dbo].[TranTest] ([id], [value]) VALUES ('1', '2');
INSERT INTO [dbo].[TranTest] ([id], [value]) VALUES ('2', '2');
INSERT INTO [dbo].[TranTest] ([id], [value]) VALUES ('3', '2');

--执行Sql语句
EXEC	@return_value = [dbo].[Test1]
		@Code = 4

执行结果：父存储过程的事务中回滚会影响子存储过程的事务