Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Multiple TRY...CATCH for multiple BEGIN/END?

Author  Topic 

edyl
Starting Member

35 Posts

Posted - 2013-01-03 : 11:31:15
Hello,

I am trying to do some error handling and logging in my stored procedure using TRY/CATCH statements. I want to then insert the values of ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE() in a row in a table for every error caught by the CATCH block into my own tError_Table. Do I have to write TRY/CATCH block for every BEGIN/END block within my stored procedure? I have multiple BEGIN/END block in my stored procedure.

I ask because this is what it says in the “Remarks” portion in the MSDN for TRY…CATCH (http://msdn.microsoft.com/en-us/library/ms175976.aspx).

Also I have not found good articles about using TRY…CATCH to insert the error values in a user defined table. If anyone is aware of such article or online blogs can you please point me towards it. Your help much appreciated.

Thanks in Advance.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-01-03 : 11:53:30
Do you want to carryt on after an error or log the error and stop.
I log the error then re raise it. It then gets logged again in any calling routine until it reaches the top level.

Here's an example of logging errors to a standard table.
http://www.nigelrivett.net/Products/DWBuilder/TraceTable.html

In a batch system I will log every statement so I always kjnow where it's got to - if that's noit feasible I'll se a contxt variable and log that with the error.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

edyl
Starting Member

35 Posts

Posted - 2013-01-03 : 14:06:20
Thanks Nigel.

In the MSDN site it says - “A TRY…CATCH construct cannot span multiple batches. A TRY…CATCH construct cannot span multiple blocks of Transact-SQL statements. For example, a TRY…CATCH construct cannot span two BEGIN…END blocks of Transact-SQL statements and cannot span an IF…ELSE construct.”

Let’s say I have a stored procedure as follows that is run by a SSIS package. I want to catch some run time errors if any and load them into my error table. Do I have to write multiple try catch blocks, one for each BEGIN/END block like shown below?


CREATE PROCEDURE MyOrderProc (@NumofDays int)
AS BEGIN

SET NOCOUNT ON;
DECLARE <few of my variable...>

DECLARE
@ErrProc varchar(100)
@ErrLine varchar(20)
@ErrDesc varchar(255)

BEGIN TRY

IF <first condition> RETURN;
ELSE
BEGIN
INSERT INTO Orders (co1....)
Select ... ...
... ... ...

UPDATE Orders
SET ... ...

END;
END TRY

BEGIN CATCH
SELECT @ErrProc = ERROR_PROCEDURE() ,
@ErrLine = ERROR_LINE() ,
@ErrDesc = ERROR_MESSAGE
SELECT @ErrProc = coalesce(@ErrProc,'') ,
@ErrLine = coalesce(@ErrLine,'') ,
@ErrDesc = coalesce(@ErrDesc,'')

INSERT tMonitor_Err (ObjectName, ErrorLine, Message)
SELECT ObjectName = @ErrProc,
ErrorLine = @ErrLine,
Message = @ErrDesc
END CATCH

BEGIN TRY

IF <another condition> RETURN;
ELSE
BEGIN
<insert/update another sets of tables>
END;
END TRY

BEGIN CATCH
SELECT @ErrProc = ERROR_PROCEDURE() ,
@ErrLine = ERROR_LINE() ,
@ErrDesc = ERROR_MESSAGE
SELECT @ErrProc = coalesce(@ErrProc,'') ,
@ErrLine = coalesce(@ErrLine,'') ,
@ErrDesc = coalesce(@ErrDesc,'')

INSERT tMonitor_Err (ObjectName, ErrorLine, Message)
SELECT ObjectName = @ErrProc,
ErrorLine = @ErrLine,
Message = @ErrDesc
END CATCH

END;
GO


Regards.
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-01-03 : 14:25:14
>>Do I have to write multiple try catch blocks, one for each BEGIN/END block like shown below?
No, you don't have to. You can have one try/catch for a bunch of blocks of statements if you want - as long as they are in the same batch (no "GO" in the middle of a try catch).

One reason you may want to have different try/catch blocks is if you have different catch logic depending on which statements fail.

Be One with the Optimizer
TG
Go to Top of Page
   

- Advertisement -