Common Mistake the DEvelopers do in the Exception Handling

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 420
Sometime, we will miss out to have the proper coding.. For handling exceptions in SQL Server, we usually making use of TRY..CATCH blocks along with TRANSACTIONs. What i would like to say is, if there is no ROLLBACK statement in the CATCH blcok, the T-SQL / Stpred Procedure will throw error (Msg 3998, Level 16, State 1, Line 1
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.)

Try the below code snippet, you will get the error message 3998
CREATE TABLE TEMP ( A INT )
GO

BEGIN TRY
BEGIN Tran
ALTER TABLE TEMP
DROP COLUMN author
COMMIT TRAN
END TRY
BEGIN CATCH
-- Execute the error retrieval routine.
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
PRINT ' *****Value of XACT_STATE ****'+CONVERT(VARCHAR,XACT_STATE())
--ROLLBACK TRAN -- Uncomment this line for avoiding error
END CATCH
GO

To resolve the issue, uncommnet the ROLLBACK TRAN statement in the CATCH block and run the T-SQL code..

Comments or Responses

Login to post response