0

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

3
Contributors
4
Replies
5
Views
8 Years
Discussion Span
Last Post by sknake
0

I am having Two Tables Category,and Product Tables

Catid is the Primary KEY for cat,

I want to count the Product List

0

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.

0

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 ...

0

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.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.