Multiple TRY...CATCH for multiple BEGIN/END? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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.

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.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources