Template SQL z wykorzystaniem transakcji i bloku TRY-CATCH

Template SQL z wykorzystaniem transakcji i bloku TRY-CATCH

USE [db]
GO

-- SET XACT_ABORT ON will cause the transaction to be uncommittable
-- when the constraint violation occurs.
SET XACT_ABORT ON;

BEGIN TRY
BEGIN TRANSACTION
----------------SQL BEGIN------------------

raiserror(N'Jakiś błąd', 16, 1); -- wyrzucenie wyjatku z sekcji try. Zostanie on przechwycony w catch (severity > 10 (np.16))

---------------------SQL END-------------
COMMIT TRANSACTION
PRINT 'The script has been run successfuly'
END TRY
BEGIN CATCH
IF (XACT_STATE()) = -1
BEGIN
PRINT
N'The script failed. Rolling back transaction.'
ROLLBACK TRANSACTION;
END

DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;

SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

-- Use RAISERROR inside the CATCH block to return
-- error information about the original error that
-- caused execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, @ErrorSeverity,  @ErrorState);
END CATCH
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s