Tranasction and TRY – CATCH in SQL SERVER

  BEGIN TRY

      BEGIN TRANSACTION  — BEGIN TRANSACTION should come before or after BEGIN TRY that is not equally important, but it feels more right to have it inside the TRY-CATCH section.

YOUR STATEMENTS COMES HERE

  COMMIT  —   Don’t write this COMMIT  after end of CATCH block because  if you get an error and wind up in the CATCH up section, you will roll back the transaction. When you exit the CATCH block, you will
attempt to commit, but there is nothing to commit, so there will be a new error.

END TRY

BEGIN CATCH

     ROLLBACK;

THROW  — THROWING the original exception to the application. THROW  statement is available in SQL server 2012. RAISEERROR() vs THROW: http://sqlhints.com/2013/06/30/differences-between-raiserror-and-throw-in-sql-server/

END CATCH

////////////////////////// other way ////////////////////////////////

 

BEGIN TRANSACTION;

BEGIN TRY
    -- Some code
END TRY

BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;

If you have code after your try catch statement that also logically belongs in the same transaction.

 

For Testing :

CREATE PROCEDURE [dbo].[USP_TEST]
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
BEGIN TRANSACTION
— CREATE TABLE TestTable ( ID int )
INSERT INTO TestTable SELECT 23
INSERT INTO SomeOtherTable ( YYYYY,XXXXX)
VALUES (”,”,”,”, ”, ”, ”) — SOME error thowing statement.
COMMIT
END TRY
BEGIN CATCH
ROLLBACK;
THROW
END CATCH
END

=================================================

If you want  call RAISERROR in every SP, we can create an SP like below and call in every SP CATCH block.

CREATE PROCEDURE [dbo].[RethrowError]
AS
-- Return if there is no error information to retrieve.
IF ERROR_NUMBER() IS NULL
    RETURN;

DECLARE 
    @ErrorMessage    NVARCHAR(4000),
    @ErrorNumber     INT,
    @ErrorSeverity   INT,
    @ErrorState      INT,
    @ErrorLine       INT,
    @ErrorProcedure  NVARCHAR(200);

-- Assign variables to error-handling functions that 
-- capture information for RAISERROR.
SELECT 
    @ErrorNumber = ERROR_NUMBER(),
    @ErrorSeverity = ERROR_SEVERITY(),
    @ErrorState = ERROR_STATE(),
    @ErrorLine = ERROR_LINE(),
    @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

-- Build the message string that will contain original
-- error information.
SELECT @ErrorMessage = 
    N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 
        'Message: '+ ERROR_MESSAGE();

-- Raise an error: msg_str parameter of RAISERROR will contain
-- the original error information.
RAISERROR 
    (
    @ErrorMessage, 
    @ErrorSeverity, 
    1,               
    @ErrorNumber,    -- parameter: original error number.
    @ErrorSeverity,  -- parameter: original error severity.
    @ErrorState,     -- parameter: original error state.
    @ErrorProcedure, -- parameter: original error procedure name.
    @ErrorLine       -- parameter: original error line number.
    );

And then use it like this:

BEGIN CATCH
    ROLLBACK TRAN
    EXEC dbo.RethrowError
END CATCH  
==================================================================


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