1001.sql

在学习SQL Server过程中整理的T-SQL语句

--打开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