下面是测试表和存储过程的代码
---创建测试表
IF EXISTS ( SELECT * FROM sys.tables WHERE name = 'tt' )
DROP TABLE dbo.tt ;
CREATE TABLE dbo.tt ( ID INT IDENTITY , Name NVARCHAR (100), TransCount INT ) ;
GO
---创建主存储过程
IF EXISTS ( SELECT * FROM sys.procedures WHERE name = 'P_proc' )
DROP PROC P_proc ;
GO
CREATE PROC P_proc
AS
BEGIN
BEGIN TRAN ;
INSERT INTO dbo.tt ( Name, TransCount ) SELECT '查询1', @@TRANCOUNT ;
EXEC dbo.S_proc ;
RETURN ;
END ;
GO
---创建子存储过程
IF EXISTS ( SELECT * FROM sys.procedures WHERE name = 'S_proc' )
DROP PROC S_proc ;
GO
CREATE PROC S_proc
AS
BEGIN
BEGIN TRAN ;
INSERT INTO dbo.tt ( Name, TransCount ) SELECT '查询2', @@TRANCOUNT ;
ROLLBACK ;
RETURN ;
END ;
执行主存储过程P_proc
分析