commiting inner query while outer transaction is rollbacked

[ankushbansal89]

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

[jaivinoth1782]

I am having Two Tables Category,and Product Tables

Catid is the Primary KEY for cat,

I want to count the Product List

[sknake]

Try putting your log query in exec('query here') and see if that rolls back. I think that may be a work around but I haven't tested it before.

[ankushbansal89]

Try putting your log query in exec('query here') and see if that rolls back. I think that may be a work around but I haven't tested it before.

Thank for the reply ...
but ur idea is not working ...
i got the another solution by using goto and label ...

[sknake]

I'm glad you found a solution to your problem and good luck!

Please mark this thread as solved if you have found an answer to your question.