Using Nested Transactions In SQL Server 2005

Recently I have done Nested transaction in SQL Server 2005.
In this post, I will show you how to use Nested Transactions.

Below is the inner procedure which will be called from another procedure.

CREATE PROCEDURE pInnerProcdureName1

 AS

 BEGIN

   BEGIN try

     BEGIN TRANSACTION

        PRINT 'pProcdureName1 : ' + cast(@@TRANCOUNT as varchar(max))     

        DELETE FROM tablename

        COMMIT TRANSACTION

      PRINT 'pProcdureName1 Completed : ' + cast(@@TRANCOUNT as varchar(max)) 

     END try

     BEGIN catch

       if @@ERROR > 0

       begin

         PRINT error_message()

       end

       rollback transaction

       print 'Rollback Transaction :' + cast(@@TRANCOUNT as varchar(max)) 

     end catch    

   end 

GO

Now creating the outer transaction:

CREATE PROCEDURE pOuterProcedureName

    as

    begin

      begin try

        begin transaction

            print 'pOuterProcedureName : ' + cast(@@TRANCOUNT as varchar(max)) 

               exec pInnerProcdureName1

               commit transaction

           print 'pOuterProcedureName Transaction Committed : ' + cast(@@TRANCOUNT as varchar(max)) 

     end try

     begin catch

       if @@TRANCOUNT > 0

       begin

           rollback transaction

       end

      print 'Rollback pOuterProcedureName  : ' + cast(@@TRANCOUNT as varchar(max))

       if @@ERROR > 0

       begin

           SELECT error_message()

       end 

     end catch    

   end

Now just execute your Outer Transaction and Done.

EXEC pOuterProcedureName

Enjoy!!!!!!

Inserting Multiple Rows in One Insert Statement In SQL SERVER 2008

You can insert multiple records with one insert statement. Syntax is:

Insert Into TableName (field1,field2)
VALUES 

(value1,value2),

(value3,value4)