--打開PUBS數據庫，創建表TestTrans
USE pubs
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TestTrans]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TestTrans]
GO

CREATE TABLE [dbo].[TestTrans] (
	[Cola] [int] NOT NULL ,
	[Colb] [char] (3) COLLATE Chinese_PRC_CI_AS NOT NULL 
) ON [PRIMARY]
GO

--創建帶參數的存儲過程TransProc，存儲過程以事務的方式向表TestTrans插入兩條記錄
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TransProc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TransProc]
GO

CREATE PROCEDURE TransProc @PriKey INT, @CharCol CHAR(3) AS
 BEGIN TRANSACTION InProc
  INSERT INTO TestTrans VALUES (@PriKey, @CharCol)
  INSERT INTO TestTrans VALUES (@PriKey + 1, @CharCol)
 COMMIT TRANSACTION InProc
GO

--開始一個嵌套事務，外層事務為OutOfProc，內層事務為存儲過程TransProc定義的事務TransProc
BEGIN TRANSACTION OutOfProc
GO
EXEC TransProc 1, 'aaa'
GO
--回滾外層事務，內層事務也將回滾
ROLLBACK TRANSACTION OutOfProc
GO
--查詢表中記錄的結果
SELECT * FROM TestTrans
--執行另外一個嵌套事務
EXECUTE TransProc 3,'bbb'
GO
--查詢表中記錄的結果
SELECT * FROM TestTrans