Error Handling For Update Statements

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

Transact-SQL (2005)

Error Handling For Update Statements

Author

Topic

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-04-06 : 06:23:12

Hi all,

I have created a Stored Procedure for update statement and implemented Error handling procedures. Please let me know if any further conditions required. is my SP perfect. What needs to be added on?

BEGIN TRANSACTION

SELECT @ErrorMessage = 'Modification to USHR_ProgressNotes table failed'

UPDATE USHR_ProgressNotes
SET
NotesName = @NotesName,
ControlType = @ControlType,
ParentNotesId = @ParentNotesId,
Sort = @Sort,
Others = @Others,
ISCOMMON = @IsCommon
WHERE
NotesId = @NotesId

IF(@@ROWCOUNT = 0)
BEGIN
RAISERROR(@ErrorMessage,16,1)
END
COMMIT
END TRY

BEGIN CATCH
IF(@@TRANCOUNT > 0)
BEGIN
ROLLBACK TRANSACTION
END

INSERT INTO USHR_ErrorLog([Date],[Time],ErrorText)
VALUES(GETDATE(),CONVERT(VARCHAR(50),GETDATE(),108),
CASE WHEN ERROR_MESSAGE() IS NULL THEN @ErrorMessage ELSE ERROR_MESSAGE() END)

RAISERROR (@ErrorMessage,16, 1);
END CATCH

Iam a slow walker but i never walk back

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-06 : 06:41:33

your stored proc is definitely not perfect.

What happens if it's inside another transaction and it hits an error.

Do you really want to rollback the whole parent transaction? Or did you actually only want to rollback the transaction inside your sp and return an error?

Have a look here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=141905

There's a bit of back and forth between Kristan and myself about saving transactions inside parent transactions.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-04-06 : 07:11:57

quote:
Originally posted by Transact Charlie

your stored proc is definitely not perfect.

What happens if it's inside another transaction and it hits an error.

Do you really want to rollback the whole parent transaction? Or did you actually only want to rollback the transaction inside your sp and return an error?

Have a look here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=141905

There's a bit of back and forth between Kristan and myself about saving transactions inside parent transactions.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Actually my stored procedure does contain only a single transaction so there is no need to worry about multiple transactions in my stored procedure

Iam a slow walker but i never walk back

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-06 : 07:24:35

yes -- my point was:

1) You start a transaction

2) you do some stuff

3) you call this stored proc

4) Your stored proc makes another transaction

5) something goes wrong.... this stored proc rolls back ALL The transaction right back to 1)

Did you really want that to happen? or did you really want to go back to 3)

issuing a ROLLBACK without a SAVE point rolls back ALL the current nested transactions. So if you are 5 transactions deep and you hit a ROLLBACK then you ROLLBACK everything to this point.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-04-06 : 07:40:32

quote:
Originally posted by Transact Charlie

yes -- my point was:

1) You start a transaction

2) you do some stuff

3) you call this stored proc

4) Your stored proc makes another transaction

5) something goes wrong.... this stored proc rolls back ALL The transaction right back to 1)

Did you really want that to happen? or did you really want to go back to 3)

issuing a ROLLBACK without a SAVE point rolls back ALL the current nested transactions. So if you are 5 transactions deep and you hit a ROLLBACK then you ROLLBACK everything to this point.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

hi,

The point is i dont call the storeprocedure inside any transaction. inturn i call transaction inside the storedprocedure. i hope iam clearing your doubts

Iam a slow walker but i never walk back

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources