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

How to use try cache in nested call of stored precedure, if one wants other sp to run even if one sp produce error. Expand / Collapse
Author
Message
Posted Tuesday, February 11, 2014 5:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 8:48 AM
Points: 48, Visits: 144
I have to call few strored precedure like sp1 sp2 sp3 from spmain.

spmain will be scheduled and it will run on time basis.
i want to trap each kind of error for loging so that i can check latter.
and at the same time i want to continue execution of other procedure like sp2 sp3, even if there is error in sp1


So I used try catch and begin catch like following,

CREATE PROC SPMAIN
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY


BEGIN TRY
EXEC SP1
END TRY
BEGIN CATCH
EXEC LOGERROR
END CATCH

BEGIN TRY
EXEC SP2
END TRY
BEGIN CATCH
EXEC LOGERROR
END CATCH


END TRY
BEGIN CATCH
EXEC LOGERROR

END CATCH


END

GO


CREATE PROC SP1
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY

.......
......
......


END TRY
BEGIN CATCH
EXEC LOGERROR
END TRY

GO


CREATE PROC SP2
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY

.......
......
......


END TRY
BEGIN CATCH
EXEC LOGERROR
END TRY

GO


Q1) In sp2 try cache i have used is looking redundent.
can i remove it?

Q2) few people suggest to remove try cache around exec sp2 from spmain.

please suggest which one good which one good Q1 Q2 or the code i have pased is good.

Q3)following line is from this link(http://technet.microsoft.com/en-us/library/ms175976(v=sql.90).aspx)
•Errors that have a severity of 20 or higher that stop the SQL Server Database Engine task processing for the session.
If an error occurs that has severity of 20 or higher and the database connection is not disrupted, TRY…CATCH will handle the error.

Q4) If some body restart sqlserver or kill the workerprocess, at that time if spmain is running, is it possible to log few vaiables value forom spmain to user defined table or out side in any file , events log etc.

any links would be help full.

Q5)I wanted to know / log error/events in user defined tables when ever errors greater
than severity of 20 comes and database connections disrupted.


yours sincerley


Post #1540156
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse