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