if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[a]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[a] GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[b]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[b] GOCREATE TABLE [dbo].[a] ( [a] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GOCREATE TABLE [dbo].[b] ( [b] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test_1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[test_1] GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test_2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[test_2] GOSET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GOCREATE 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 GOSET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GOCREATE 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是全局变量呀
调试了一下: if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[a]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[a] GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[b]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[b] GOCREATE TABLE [dbo].[a] ( [a] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GOCREATE TABLE [dbo].[b] ( [b] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test_1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[test_1] GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test_2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[test_2] GOSET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GOCREATE 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 GOSET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GOCREATE 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 GOexec test_2
drop table [dbo].[a]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[b]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[b]
GOCREATE TABLE [dbo].[a] (
[a] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[b] (
[b] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test_1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[test_1]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test_2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[test_2]
GOSET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GOCREATE 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
GOSET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GOCREATE 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是全局变量呀
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[a]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[a]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[b]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[b]
GOCREATE TABLE [dbo].[a] (
[a] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[b] (
[b] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test_1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[test_1]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test_2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[test_2]
GOSET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GOCREATE 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
GOSET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GOCREATE 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
GOexec test_2