Exception Handling and Transaction with Sybase 12.5

By S.Luann

I was suprised all the way to apply exception handling and transaction control for my stored procedure with Sybase 12.5.



CREATE PROC P1
BEGIN

DECARE @myerr int

BEGIN TRANS mytrans

DELETE FROM T1
IF @@error<>0 BEGIN SELECT @myerr=@@error GOTO failed END

INSERT INTO T2...
IF @@error<>0 BEGIN SELECT @myerr=@@error GOTO failed END

UPDATE T3
IF @@error<>0 BEGIN SELECT @myerr=@@error GOTO failed END

COMMIT TRANS mytrans
RETURN 0

failed:
ROLLBACK TRANS mytrans
retrun @myerr

END


It looks tidious and stupid enough because that a DML statement in a transaction failed should sponteniously Rollback the transaction and the left statements in the same transaction should be aborted. It doesn't do this way with Sybase 12.5 and SQL 2000. Some statements just not Rollback transaction when it encounters an error. the following statements get executed as nothing happed with the exceptioned statement. Based [1] says "it is not the statements' responsibility to RollBack the transaction."


Fortunately, with SQL 2005, we don't need to do the stupid thing, because the elegant try ... catch block:


BEGIN TRY
INSERT T1 ...
EXECUTE usp_ExampleProc
DELETE T2 ...
COMMIT TRANSACTION
END TRY

BEGIN CATCH

SELECT
ERROR_NUMBER() as ErrorNumber,
ERROR_MESSAGE() as ErrorMessage;
ROLLBACK TRANSACTION
RAISE ERROR ...
END CATCH;




@@error
Very volatile, be changed in any statement. better to save to a local var after a statement

ROLLBACK TRANSACTION

ROLLBACK TRANSACTION statement without a transaction name or save point name specified will rollback to the outermost transaction. In other words, after it @@transcount is set to 0.

How transaction works bebind the scene
Database has transaction log, where the new record and the old one are persisted until a transaction is committed or rolled back.







Reference

[1] Erland Sommarskog, Implementing Error Handling with Stored Procedures,
http://www.sommarskog.se/error-handling-II.html#firstexample

[2] Kevin Kline, Transact SQL Programming
http://books.google.ca/books?id=CWf1Pv6OeAcC&printsec=frontcover

[3] Transact-SQL User's Guide
http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/54715;pt=52735/*

[4] SQL Server 2005 Try and Catch Exception Handling,
http://www.mssqltips.com/tip.asp?tip=1027