Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Error Handling in Nested Procedures and Logging Custom Errors Expand / Collapse
Author
Message
Posted Thursday, July 02, 2009 10:00 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 5:55 AM
Points: 56, Visits: 287
Hi All,

My Nested code is similar to the below code template

CREATE PROCEDURE usp_Proc1
AS
BEGIN TRY
BEGIN TRAN TRAN1

EXEC usp_Proc2
COMMIT TRAN TRAN1
END TRY
BEGIN CATCH
ROLLBACK TRAN TRAN1
END CATCH
---------------------------------------------------
CREATE PROCEDURE usp_Proc2
AS
BEGIN TRY
BEGIN TRAN TRAN2

EXEC usp_Proc3

COMMIT TRAN TRAN2
END TRY
BEGIN CATCH
ROLLBACK TRAN TRAN2
END CATCH
---------------------------------------------------
CREATE PROCEDURE usp_Proc3
AS
BEGIN TRY
BEGIN TRAN TRAN3
SOURCE CODE ----
COMMIT TRAN TRAN3
END TRY
BEGIN CATCH
ROLLBACK TRAN TRAN3
END CATCH
---------------------------------------------------
In summary, usp_Proc1 calls usp_Proc2 which in turn calls usp_Proc3.

I need to log all the error that might occur in above flow and also the source(proc_name) of error with description.

Regards,



Post #746757
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse