Error handling and rollback - 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

Error handling and rollback

Author

Topic

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2007-06-04 : 20:11:39

Hi,

I have a cursor that deletes records based on the values from another table.

I need to check if any error happens in the delete statement, then i need to roll back all the transactions and display the error message.
Else if there are no errors then i have to commit the tranactions.

How do i check for this and where do i put these conditions.. I need to apply this logic for other scripts too.

Please advise..

Thanks

Below is my query
=================================================
DECLARE Tempcursor1 CURSOR
FOR SELECT ID FROM SITE

DECLARE @ID uniqueidentifier

OPEN Tempcursor1
FETCH NEXT FROM Tempcursor1
INTO @ID

WHILE @@Fetch_Status = 0
BEGIN
DELETE FROM BaseTable
WHERE Id = @ID

DELETE FROM AddBASE
WHERE PARENTID = @ID

FETCH NEXT FROM Tempcursor1
INTO @ID
END

CLOSE Tempcursor1
DEALLOCATE Tempcursor1

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-04 : 22:05:37

Which version of SQL Server are you using? In 2005, there is TRY/CATCH block available to trap errors. in 2000, you have to check for @@ERROR > 0, if so rollback the transaction.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Kristen
Test

22859 Posts

Posted - 2007-06-05 : 04:15:34

"I need to check if any error happens in the delete statement"

What sort of error are you wanting to catch?

You can detect that the DELETE actually "deleted" ZERO rows.

Or that an @@ERROR code was returned, but I am not sure that's ever going to catch anything useful - perhaps that a Delete Trigger forced a rollback.

But I don't think anything is going to catch a Deadlock Candidate, nor a Syntax/Runtime error (e.g. "Table does not exist")

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-05 : 09:50:44

Also refer
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html

Madhivanan

Failing to plan is Planning to fail

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2007-06-05 : 16:16:52

Dinakar..I am using SQL 2005 version. Is there any good websites that has examples that i can follow for TRY/CATCH...

Kristen.. I am trying to find out any errors that may cause due to delete statement.. for eg,
if there is a reference of this record in another table this delete statement may fail. In this
case i want to roll back the transactions.

Madhivanan.. Thanks for the website..i am looking into it.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-05 : 16:31:45

If you want to trap the errors for DELETE of each PK then you have to use a loop. If its a batch delete and you want to delete all or none, then you dont need a loop. Just use a Begin tran/End tran and check for @@ERROR and either commit or rollback the tran.
Check books on line for the try/catch block. (and ofcourse google).

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Kristen
Test

22859 Posts

Posted - 2007-06-05 : 17:06:15

Something like this perhaps:


CREATE TABLE MyParent
(
	ParentID	INT NOT NULL,
	PRIMARY KEY
	(
		ParentID
	)
)
GO
CREATE TABLE MyChild
(
	ParentID	INT NOT NULL,
	ChildID		INT NOT NULL,
	PRIMARY KEY
	(
		ParentID,
		ChildID
	)
)
GO

ALTER TABLE dbo.MyChild WITH NOCHECK ADD CONSTRAINT
	FK_Test FOREIGN KEY
	(
	ParentID
	) REFERENCES dbo.MyParent
	(
	ParentID
	)
GO

INSERT INTO MyParent(ParentID)
SELECT	1000 UNION ALL
SELECT	2000 UNION ALL
SELECT	3000

INSERT INTO MyChild(ParentID, ChildID)
SELECT	1000, 1001 UNION ALL
SELECT	1000, 1002 UNION ALL
SELECT	2000, 2001 UNION ALL
SELECT	2000, 2002 UNION ALL
SELECT	3000, 3001 UNION ALL
SELECT	3000, 3002
GO

PRINT 'Start delete test'
GO
SET NOCOUNT ON
BEGIN TRANSACTION

DECLARE	@intError	int,
	@intRowCount	int

DELETE	D
FROM	MyParent AS D
WHERE	ParentID = 1000
SELECT	@intError = @@ERROR, @intRowCount = @@ROWCOUNT

IF @intError <> 0 OR @intRowCount <> 1
BEGIN
	ROLLBACK
	PRINT 'Rollback'
END
ELSE
BEGIN
	COMMIT
	PRINT 'Commit'
END
SET NOCOUNT OFF
GO

PRINT 'End delete test'
GO

DROP TABLE MyChild
GO
DROP TABLE MyParent
GO

Kristen

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2007-06-05 : 17:34:01

Dhinakar Thanks !!..

Kristen.. Thanks for the sample code.. This helps me !..

Kristen
Test

22859 Posts

Posted - 2007-06-06 : 03:11:59

In my experience the biggest problem with this is that the Error Message generated by SQL Server may be caught by the Application, and abort the application!

It also make sit look like there was a problem which needs attention, whereas in fact the error has been trapped and dealt with.

Other errors (such as syntax errors) may thus go unnoticed

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources