a question about Transaction - 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

SQL Server 2000 Forums

Transact-SQL (2000)

a question about Transaction

Author

Topic

hasanali00
Posting Yak Master

207 Posts

Posted - 2004-12-26 : 04:28:03

Hi,
I have the following code in my Stored Procedure:

BEGIN TRANSACTION AddCustomer

Insert statement 1
Insert statement 2

COMMIT TRANSACTION AddCustomer
GO

If the first Insert statement fails, then I do not want the second Insert statement to take place.

But when I run this SP, if the first Insert fails, the second Insert still goes ahead. My initial idea was that because I am using TRANSACTION, if any Insert statement fails, the whole Transaction would fail, but obviously this is not happening.

So how can I write this SP, so that if one Insert fails, the then every other Insert fails.

regards

Kristen
Test

22859 Posts

Posted - 2004-12-26 : 05:52:24

Something like:


BEGIN TRANSACTION AddCustomer

Insert statement 1
IF @@ERROR <> 0 OR @@ROWCOUNT <> 1
BEGIN
    ROLLBACK
    GOTO MY_EXIT
END
Insert statement 2
IF @@ERROR <> 0 OR @@ROWCOUNT <> 1
BEGIN
    ROLLBACK
    GOTO MY_EXIT
END

COMMIT TRANSACTION AddCustomer
GOTO MY_EXIT:
GO

hasanali00
Posting Yak Master

207 Posts

Posted - 2004-12-26 : 16:09:36

Thanks u.
I did not think I would get an answer on Boxing Day

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-26 : 16:10:52

How about:

SET XACT_ABORT ON
BEGIN TRANSACTION AddCustomer

Insert statement 1
Insert statement 2

COMMIT TRANSACTION AddCustomer

rockmoose

Kristen
Test

22859 Posts

Posted - 2004-12-27 : 02:26:06

"I did not think I would get an answer on Boxing Day"

Sad isn't it! However, I was on call ...

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-27 : 03:54:52

Sad isn't it! However, I was on call ...
Oh, I was on call too

*BG*

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources