whats wrong with this.........

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

whats wrong with this.........

Author

Topic

vipin7sep
Starting Member

1 Post

Posted - 2009-08-26 : 06:33:40

Create proc a
as
begin tran
print @@trancount
rollback tran
print @@trancount
if @@trancount > 0
begin
Commit tran
end
go

Create proc b
as
begin tran
print @@trancount
exec a
print @@trancount
if @@trancount > 0
begin
Commit tran
end
go

Exec b

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2009-08-26 : 07:38:59

Hi,
some facts:
1. There are no autonomous transactions in MS SQL Server like in Oracle.
2. We cannot rollback nested transaction. rollback statement rolls back outermost transaction.
3. If rollback statement have a name, it rolls back to savepoint with such name or outermost transaction with such name.

Now try this

alter proc a
as
declare @trana as varchar(10)
SELECT @trana='Ta'
declare @tranb as varchar(10)
SELECT @tranb='Tb'
SET IMPLICIT_TRANSACTIONS ON

begin tran @trana
save transaction @trana
print @@trancount
rollback tran @trana;
print @@trancount
if @@trancount > 0
begin
Commit tran @tranb
end
go

alter proc b
as
declare @tranb varchar(10)
SELECT @tranb='Tb'

begin transaction @tranb
print @@trancount
exec a
print @@trancount
if @@trancount > 0
begin
Commit transaction @tranb
end
go

Exec b

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources