Begin transaction ........... commit transaction

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Begin transaction ........... commit...

Begin transaction ........... commit transaction

Rate Topic

Display Mode

Topic Options

Author

Message

river1

Posted Thursday, March 25, 2010 10:39 AM

Say Hey Kid

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:56 AM
Points: 679, Visits: 953

Hi,

I have this stored procedure:

create stored procedure test
as

Begin

update table xtu set cod ='A'

End

if i execute this stored procedure from inside other stored procedure, like this:

create procedure test2
as
BEGIN

declare @erro as int

begin transaction

exec test
set @erro =@@error

if @erro =0
begin
commit transaction
end
else
rollback transaction
END

If i get an error when executing the procedure test can i rollback the transaction that was made by the procedure test? or should i place a BEGIN TRANSACTION and COMMIT TRANSACTION inside the stored procedure test , so that i can rollback transaction from within procedure test2 if procedure test fails?

Thank you

Post #889999

SQLRNNR

Posted Thursday, March 25, 2010 10:42 AM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 10:25 AM
Points: 18,754, Visits: 12,337

Look up Try Catch in BOL. That will help you with being able to rollback the transaction.

Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #890008

GTR

Posted Friday, March 26, 2010 3:24 PM

Old Hand

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 3:15 PM
Points: 389, Visits: 351

Check this, it should answer your question

http://msdn.microsoft.com/en-us/library/ms189336(SQL.90).aspx

EnjoY!

Post #891133

SQLRNNR

Posted Friday, March 26, 2010 11:29 PM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 10:25 AM
Points: 18,754, Visits: 12,337

GTR (3/26/2010)

Check this, it should answer your question

http://msdn.microsoft.com/en-us/library/ms189336(SQL.90).aspx

This method would allow the OP to place a transaction in both stored procedures (thus nest the transactions).

Post #891256

« Prev Topic | Next Topic »

Permissions