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.
Multiple TRY...CATCH for multiple BEGIN/END?
Posted - 01/03/2013 : 11:31:15
