Creating Transactions Within a Stored Procedure

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 2000 Forums

Transact-SQL (2000)

Creating Transactions Within a Stored Procedure

Author

Topic

salmonraju
Yak Posting Veteran

54 Posts

Posted - 2006-10-25 : 09:14:33

hello,
here is my code ,i have two tables "orders","authors"
please observe the code once
in order to raise exception i deleted authors table my code is:
Create Proc TranTest3
AS
BEGIN TRAN
update order1 set orderName='yyy' where id=4
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
return 10
END
UPDATE authors
SET au_fname = 'Johnzzz'
WHERE au_id = '172-32-1176'
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
return 11
END
COMMIT TRAN
GO

i executed this code using Exec TranTest3
my code is not reaching either commit or abort so query not reaching end
ERRor message is :Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.

please suggest me where i am getting worng

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-10-25 : 10:50:45

If you have 2 BEGIN TRAN statements, you need 2 COMMIT statements (OR ROLLBACKs) that can be hit by the code.

Create Proc TranTest3
AS
BEGIN TRAN
update order1 set orderName='yyy' where id=4
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
return 10
END

ELSE
BEGIN
COMMIT TRAN 
END

UPDATE authors
SET au_fname = 'Johnzzz'
WHERE au_id = '172-32-1176'
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
return 11
END
COMMIT TRAN
GO

The ELSE and BEGIN/END are not necessary, but i use them anyways.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-10-25 : 11:19:38

quote:
Originally posted by DonAtWork

If you have 2 BEGIN TRAN statements, you need 2 COMMIT statements (OR ROLLBACKs) that can be hit by the code.

While this is true ... I only see 1 begin transaction in this proc.

Jay White

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-25 : 11:36:38

Attempting an operation on a table that doesn't exist is a "non-trappable" error, so your error checking code doesn't get executed.

If it is normal for a table to not exist, then you should add code to check for the existence of the table before executing the update.


if objectproperty(object_id('dbo.authors'),'IsUserTable') = 1
	begin

	update dbo.authors
	set
		au_fname = 'Johnzzz'
	where
		au_id = '172-32-1176'

	if @@error <> 0
		begin
		rollback transaction
		return 11
		end

	end

CODO ERGO SUM

salmonraju
Yak Posting Veteran

54 Posts

Posted - 2006-10-26 : 01:41:57

Hi DonAtWork,
i fell that ur procedure is restricted to single command
in your code , when the first statements executed sucessfully ur code reaching the commit
But commit need to reached after every command in the procedure executed successfuly

salmonraju
Yak Posting Veteran

54 Posts

Posted - 2006-10-26 : 03:10:31

hi Michael Valentine Jones
i created 2nd object i.e, departments
later i tested my code in debugging mode using visual studio
IMPORTANT POINT is when i am deleting a row that that doesnt exit @@ERROR is still 0,
though operation failed .

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-10-26 : 07:27:30

whoops. i read a second BEGIN TRAN in there somehow.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-26 : 07:53:01

quote:
Originally posted by salmonraju

hi Michael Valentine Jones
i created 2nd object i.e, departments
later i tested my code in debugging mode using visual studio
IMPORTANT POINT is when i am deleting a row that that doesnt exit @@ERROR is still 0,
though operation failed .

So what does that have to do with the problem you posted?

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources