SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Multiple TRY...CATCH for multiple BEGIN/END?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

edyl
Starting Member

32 Posts

Posted - 01/03/2013 :  11:31:15  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3355 Posts

Posted - 01/03/2013 :  11:53:30  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.

Edited by - nigelrivett on 01/03/2013 11:53:49
Go to Top of Page

edyl
Starting Member

32 Posts

Posted - 01/03/2013 :  14:06:20  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
5924 Posts

Posted - 01/03/2013 :  14:25:14  Show Profile  Reply with Quote
>>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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000