begin tran/commit tran - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

begin tran/commit tran

Author

Topic

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2009-03-12 : 04:37:20

Hi,


create procedure bat1
as
begin
set nocount on
Declare @sample table (t varchar(20))
begin tran
	begin

	insert into @sample
	select 'stat1'

	select 1/0 --Divide by zero error encountered.

	insert into @sample
	select 'stat11'

	end
rollback tran

begin tran
	begin
	
	insert into @sample
	select 'stat2'
	
	end
commit tran

select * from @sample
set nocount off
end

when I execute the stored procedure Exec bat1,the below output comes:


-----------
Msg 8134, Level 16, State 1, Procedure bat1, Line 12
Divide by zero error encountered.

t
--------------------
stat1
stat11
stat2

Since "Divide by zero error encountered" have encountered,how the rollbacked transaction ie stat1,stat11 value is getting stored in @sample table variable

Actually it should get rollback, and the stat2 alone need to be stored na.please correct if iam wrong

shaggy
Posting Yak Master

248 Posts

Posted - 2009-03-12 : 05:01:10

include return after the rollback tran

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2009-03-12 : 05:35:25

If i put return then it would not execute the second part/statement ie is



begin tran
	begin
	
	insert into @sample
	select 'stat2'
	
	end
commit tran

quote:
Originally posted by shaggy

include return after the rollback tran

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources