About Me

Barking, Essex, United Kingdom
MCITP DBA ,MCITP BI & Oracle OCP 11G

Monday, April 15, 2013

Error Handling

USE test
Create procedure proc1 as
Begin
BEGIN TRY
    BEGIN TRAN t1 with  mark 'IC3046'     
    delete from t1 where id=3
    COMMIT TRAN t1
END TRY
BEGIN CATCH
      --Comment out or remove as required
      --TOBE USED when running as a stand alone script
      SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage,
        ERROR_LINE() AS ErrorLine,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState;
    --Comment out or remove as required
    --TOBE USED when running as a Stored Procedure etc
      DECLARE @ErrorMessage VARCHAR(4000);
      DECLARE @ErrorSeverity INT;
      DECLARE @ErrorState INT;
      SELECT
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();
       RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState); -- State.
      --Comment out or remove as required
      --To be used if a rollback is needed on any error
    IF (XACT_STATE()) <> 0
    BEGIN
        PRINT 'There has been an Error, see error message. Rolling back transaction.'
        ROLLBACK TRAN t1
END
      --Comment out or remove as required
      --To be used if wishing to commit on non critical errors
    IF (XACT_STATE()) = -1
    BEGIN
        PRINT 'The transaction is in an  non committable state. Rolling back transaction.'
        ROLLBACK TRAN t1
    END
    IF (XACT_STATE()) = 1
    BEGIN
        PRINT 'The transaction is committable. Committing transaction.'
        COMMIT TRAN   t1
    END 
END CATCH
End
--Execute the store procedure as follows and this store procedure will delete one record from table t1
exec proc1

No comments:

Post a Comment