有显式事务的两个存储过程的相互调用

[dj0109]

现在我有一个这样的问题：我在是使用存储过程的时候遇到这样的问题，我这两个存储过程都用了Begin tran 和rollback tran，commit tran，在一个存储过程中对另外一个调用，在调用的过程中出现这样的错误报告：当前的@@transcount是0，但它应该是1，我在调试的时候发现：我在启动第一个事务是@@transcount增加1，但是当它在调用第二个存储过程是，当运行到begin tran 是它的@@transcount没有象预期的那样加1，所以在被调用的存储过程中遇到commit tran 是@@transcount就减1，变成0，回到第一个存储过程的commit tran 时，就出现那样的错误！请高手指点！

[zjcxc]

最好将代码贴出来看看

[LoveSQL]

应该是@@transcount有个作用域的问题吧

[dj0109]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[a]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[a]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[b]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[b]
GO

CREATE TABLE [dbo].[a] (
[a] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[b] (
[b] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test_1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[test_1]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test_2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[test_2]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE test_1 AS
begin
declare @transcount int
select @transcount =@@trancount
if @transcount>0
save tran tran_1
else
begin tran tran_1

insert into b (b) values ( 'b')
if @@error<>0
begin
rollback tran tran_1
return
end
else
commit tran tran_1
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE test_2 AS
begin
declare @transcount int
select @transcount =@@trancount
if @transcount>0
save tran tran_2
else
begin tran tran_2

insert into a (a) values ( 'a')
exec test_1
if @@error<>0
begin
rollback tran tran_2
return
end
else
commit tran tran_2
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

简单的代码，@@transcount是全局变量呀

[siemun2002]

调试了一下：
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[a]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[a]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[b]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[b]
GO

CREATE TABLE [dbo].[a] (
[a] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[b] (
[b] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test_1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[test_1]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test_2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[test_2]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE test_1 AS
begin
declare @transcount int
select @transcount =@@trancount
if @transcount>0
begin
save tran tran_1
print @transcount
end
else
begin
begin tran tran_2
insert into b (b) values ( 'b')
if @@error<>0
begin
rollback tran tran_2
return
end
else
commit tran tran_2
end
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE test_2 AS
begin
declare @transcount int
select @transcount =@@trancount
if @transcount>0
save tran tran_2
else
begin tran tran_2

insert into a (a) values ( 'a')
exec test_1
if @@error<>0
begin
rollback tran tran_2
return
end
else
commit tran tran_2
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

exec test_2