I have written a complex stored procedure which have a log table which keeps the track of success/failure of each and every query in the stored procedure. If there is any error in any of the query entire transaction is rollback . while rollingback, data in log table is also rollback .so if anybody plz suggest a way to commit the log file while rollbacking of outer loop.
CREATE PROCEDURE TEST AS BEGIN TRY BEGIN TRANS T1 UPDATE TABLE1 SET MyColumn = 'ABC' -- SOME QUERY --SOME QUERY --SOME QUERY BEGIN TRANS T2 --UPDATE IN LOG TABLE COMMIT TRANS T2 --SOME QUERY --SOME QUERY END TRY BEGIN CATCH ROLBACK TRANS T1 END CATCH COMMIT TRANS T1 GO
thanks in advance