Error Handling - 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

SQL Server Development (2000)

Error Handling

Author

Topic

Maracatu
Starting Member

13 Posts

Posted - 2003-05-15 : 11:18:27

I know its a tedious topic but ...

I have a sproc which has numerous inserts in it which I have put into a single transaction. If any single one of these inserts fails I want to ROLLBACK the whole transaction.
Is there any way that I can get this to happen without having to check @@ERROR after every single INSERT?

eg..
BEGIN TRAN
INSERT 1 ...
INSERT 2 ...
INSERT 3 ...
COMMIT TRAN

(I come from an Oracle background and in Oracle stored procedures, if an error occurs, execution of the procedure automatically jumps to an error handling section which you can define at the end of the procedure)

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-15 : 11:25:50

Nope.

DECLARE @Error int, @Result int

INSERT INTO...

SELECT @Error = @@ERROR, @Result = @@ROWCOUNT

IF @Error <> 0
BEGIN
--Error handling code
ROLLBACK TRAN
Return -1
END

If @Result = 0 --No Rows inserted..or som business logic error
BEGIN
--Error handling code
ROLLBACK TRAN
Return -1
END

-- ect..

I like to GOTO an Error Handling Label though

Brett

8-)

Maracatu
Starting Member

13 Posts

Posted - 2003-05-15 : 11:33:27

Thanks Brett

That's kind of what I had but wanted to avoid...
Its not so bad for one sproc but I've got a whole bunch to write and before I did so I thought I'd check if there was an easier way.

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-15 : 12:48:34

Actually, I take that back.

What I gave you is how I handle it.

But I believe that if one fails with in an a single transaction, they will all rollback.

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-15 : 12:59:22

Well Nope...I'm wrong about that last one, You have to manage your COMMITS and ROLLBACKS and you need to check and see if it's successful, or it failed.

Here's a test:




CREATE TABLE myTable (col1 int NOT NULL, col2 char(1))
GO

ALTER TABLE myTable WITH NOCHECK ADD PRIMARY KEY  CLUSTERED 
	(
		[col1]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

INSERT INTO myTable(col1,col2) SELECT 1, 'A'
GO

SELECT col1, col2 FROM myTable
GO

BEGIN TRAN
	INSERT INTO myTable(col1,col2) SELECT 2, 'B'
	INSERT INTO myTable(col1,col2) SELECT 3, 'C'
	INSERT INTO myTable(col1,col2) SELECT 4, 'D'
	INSERT INTO myTable(col1,col2) SELECT 1, 'A'
COMMIT TRAN
GO

SELECT col1, col2 FROM myTable
GO

SELECT @@TRANCOUNT

DROP TABLE myTable
GO

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-15 : 13:04:05

Here,

Check out this thread..

ooops, forget the thread:

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=23859

Brett

8-)

Edited by - x002548 on 05/15/2003 13:39:05

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-05-15 : 13:37:13

Check the topic "SET XACT_ABORT ON | OFF" in the Books online, might be just what you need.

OS

Maracatu
Starting Member

13 Posts

Posted - 2003-05-15 : 14:32:41

cool - that's just what I was looking for!

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-15 : 15:57:38

Nifty, but I still think you're better off making sure you do error checking. You might have business rules that you have to handle.

I still don't like messing with settings. You may end up trying to rember:

"Are they on or are the off?"

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources