USE [TestDB]
GO
/****** Object: Table [dbo].[test] Script Date: 02/17/2013 15:44:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[test](
[ID] [bigint] NOT NULL,
[UserID] [bigint] NULL,
[Name] [varchar](50) NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
我们常规的写一个插入的子存储过程如下:
USE [TestDB]
GO
/****** Object: StoredProcedure [dbo].[innertranv1] Script Date: 02/17/2013 15:46:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--内层事务存储过程,演示如何处理才能在嵌套的事务存储过程中正确处理事务
ALTER PROCEDURE [dbo].[innertranv1]
@ID BIGINT ,
@UserID BIGINT ,
@Name VARCHAR(50)
AS
BEGIN
SET XACT_ABORT ON
BEGIN TRAN
IF(EXISTS(SELECT TOP 1 * FROM dbo.test WHERE ID=@ID))
BEGIN
ROLLBACK
RETURN 0 ;
END
--业务逻辑开始
INSERT dbo.test
( ID, UserID, Name)
VALUES ( @ID,
@UserID,
@Name
)
--业务逻辑结束
IF @@error 0
BEGIN
ROLLBACK
RETURN 0;
END
COMMIT
SET XACT_ABORT OFF;
RETURN 1 ;
END
调用的父存储过程如下:
USE [TestDB]
GO
/****** Object: StoredProcedure [dbo].[outertranv2] Script Date: 02/17/2013 16:09:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[outertranv2]
@ID BIGINT ,
@UserID BIGINT ,
@Name VARCHAR(50)
AS
BEGIN TRAN
DECLARE @result INT
EXEC @result = innertranv1 @ID =@ID, @UserID =@UserID, @Name = @Name
IF ( @result