CODEWITHSUNDEEP
sqlsql-servert-sqlstored-proceduressql-server-2012
Stackyboy
Stackyboy

Reputation: 55

Write stored procedure so if one statement fails it should not effect the other?

I have this procedure which basically insert data. 

    Begin Transaction
                Insert into [dbo].Values
                    (
                        EQ



                    )
                    values 
                    (
                        @EQ


                    )

                  End

            --Set @STATUSRet= 'Created'
            --Set @ErrorRet= ''

        Commit Transaction
    End Try
    Begin Catch

        Set @STATUSRet= 'Failed'
        Set @ErrorRet= (Select ERROR_MESSAGE())

        Rollback Transaction

    End Catch

Now I want to add a piece of code that calls another database server and insert data into the table in that server i.e. remotely. That's ok I will do that but if that fails then that should not effect my current process of inserting the data as I have described above i.e. if the remote data insertion fails it should not effect the prior insert in any way and should return successfully to the calling application behaving like nothing happened.

Upvotes: 0

Views: 801

Answers (1)

gotqn
gotqn

Reputation: 43646

The default method of controlling transactions is auto-commit:

Any single statement that changes data and executes by itself is automatically an atomic transaction. Whether the change affects one row or thousands of rows, it must complete successfully for each row to be committed. You cannot manually rollback an auto-commit transaction.

So, if the two inserts are not wrapped in explicit transaction this will be the behavior. If you have more code blocks, then you can use two separate explicit transactions blocks like this:

DECLARE @ExecuteSecondTransaction BIT = 0;

-- local database
BEGIN TRY

    BEGIN TRANSACTION;

        -- CODE BLOCK GOES HERE

        SET @ExecuteSecondTransaction = 1;

    COMMIT TRANSACTION;

END TRY
BEGIN CATCH 

   IF @@TRANCOUNT > 0
   BEGIN
      ROLLBACK TRANSACTION
   END;

   -- GET ERRORS DETAILS OR THROW ERROR

END CATCH;

-- remote database
IF @ExecuteSecondTransaction = 1
BEGIN

    BEGIN TRY

        BEGIN TRANSACTION;
        -- CODE BLOCK GOES HERE
        COMMIT TRANSACTION;

    END TRY
    BEGIN CATCH 

       IF @@TRANCOUNT > 0
       BEGIN
          ROLLBACK TRANSACTION
       END;

       -- GET ERRORS DETAILS OR THROW ERROR

    END CATCH;

END;

Upvotes: 2

Related Questions