none
Transaction in SQL Server

    Question

  • Hello Guys,

    What is the standardized approach for using transaction in SQL Server?

    I have a stored procedure that has multiple steps and i want those to be enclosed within transaction.

    I googled the approaches and i found out the following types:-

    1. This is the first way we can use transactions:-

    CREATE PROCEDURE spNewStoredProcedure1

    @Parameter1 INT

    BEGIN

    BEGIN TRANSACTION

    /* Step 1 */

    IF @@ERROR <> 0
          BEGIN
              RETURN
          END

    /* Step 2 */

    IF @@ERROR <> 0

    BEGIN

    RETURN

    END

    COMMIT TRANSACTION

    END

    2. This is the second way we can use transactions:-

    CREATE PROCEDURE spNewStoredProcedure1

    @Parameter1 INT

    BEGIN

    BEGIN TRANSACTION

    BEGIN TRY

    /* Step 1 */

    /* Step 2 */

    COMMIT TRANSACTION

    END TRY
        BEGIN CATCH

            /* Rollback the transaction */
            ROLLBACK TRANSACTION

        END CATCH

    END

    Which is the better way of using the transactions?

    Is try and catch native to SQL Server or is it part of CLR integration.

    Please tell me why one is better than the other.
    Is there a performance benefit?
    Please elaborate you answer.

    Thanks in advance
    Wednesday, July 24, 2013 8:25 PM

Answers

All replies

  • I would recommend 2nd approach with rollback in catch block

    Reason : in case of any issue transaction is rolled back, support you are inserting 100 records and there is a exception at 15th row, all the 14 rows will be rolled back and you can re-run the batch after fix

    Yes, Try catch is native from SQL 2005 onwards

    Not really there is performance benefits in small queries but surely in case of complex steps and queries, as in the first approach you are checking @@error at evey point but in case of try catch you dont need to worry about that

    See the below link for details 

    http://msdn.microsoft.com/en-us/library/ms175976.aspx


    Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    Wednesday, July 24, 2013 11:03 PM
  • NEITHER. The goal of SQL is to do everything in ONE declarative statement, not to mimic procedural code.Oh, putting "sp_" prefixes on a procedure name is called "tibling" and it is bad. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, July 25, 2013 1:08 AM
  • Try/Catch error handling is much better. Take a look at this sample in part of using error checking

    How to insert information into multiple related tables and return ID using SQLDataSource

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, July 25, 2013 3:07 AM
  • Oh, putting "sp_" prefixes on a procedure name is called "tibling" and it is bad. 

    "tibling" - Is that a typo? I googled it and only found comments by you.


    www.minidba.com | Sql Server & Azure Db Performance Tuning & Monitoring Software | Free Lite Edition

    Thursday, July 25, 2013 6:25 AM