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
GOthanks in advance
