December 15, 2011 at 7:10 am
I have a procedure that contains several execs of other procedures. What would be the best practice of implementing try... catch blocks? Should each proc have its own try/catch, or just one in the main proc would be sufficient?
Thanks
December 15, 2011 at 7:36 am
I don't think that there's any hard and fast rule. It depends mostly on what your business needs are. If you need a lot of detail, then you'll probably want to have a try/catch block in each stored procedure or even for each statement within each stored procedure. If you don't need a lot of detail, then only having a try/catch block in the main procedure should be fine. You should be aware that some errors can't be caught within a procedure, but can be caught by a calling procedure. For those errors, you will need a try/catch block in the main procedure. (I don't remember offhand what those errors are.)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 15, 2011 at 7:41 am
I don't know if its considered best practice, but I use a single try-catch block and return codes
DECLARE @RTN INT;
DECLARE @ErrMsg NVARCHAR(4000);
BEGIN TRY
BEGIN TRAN
EXEC @RTN = Proc1
IF (@RTN != 0 )
RAISERROR('proc1 failed: RTN Code %d,', 16, 1, @RTN);
EXEC @RTN = Proc2
....
COMMIT TRAN
END TRY
BEGIN CATCH
SELECT @ErrMsg = ERROR_MESSAGE();
IF @@TRANCOUNT > 0
ROLLBACK TRAN;
RAISERROR ('Failure Message %s',16,1, @ErrMsg);
END CATCH
December 15, 2011 at 7:51 am
It depends on whether any of the sub-procs will ever be used independent of the parent proc, whether any of them have different error handling needs from others of them, and factors like that.
If, for example, an error in Proc1 means the whole thing needs to be aborted, rolled back, and an alert sent to a DBA, but Proc2 just needs to log the error and pass control back to the parent proc, then you definitely need Try Catch in each.
The other point is, that if all the error handling is in the parent proc, and one of the sub-procs is called by any other procedure or connection, then you have no error handling or, at best, inconsistent error handling. Because I assume that a proc might be used elsewhere in the future than where I originally intended, I generally prefer to have each proc do its own error handling, and have the parent proc handle errors that occur at that level.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 15, 2011 at 8:27 am
Thank you DA, TB & GS!
The reason I asked is that in the past I had a problem with try/catch when parent proc did not catch the error in the internal one. By the way, did anybody have an experience in using @@RowCount within the Catch block?
December 15, 2011 at 8:44 am
valeryk2000 (12/15/2011)
Thank you DA, TB & GS!The reason I asked is that in the past I had a problem with try/catch when parent proc did not catch the error in the internal one. By the way, did anybody have an experience in using @@RowCount within the Catch block?
I have found that as long as you capture it in another variable, its OK to use within catch. Declare @rc above the try/catch block - and use as follows. Of course its set back to zero after the rollback.
BEGIN CATCH
SELECT @ErrMsg = ERROR_MESSAGE();
SELECT @rc = @@ROWCOUNT;
IF @@TRANCOUNT > 0
BEGIN
PRINT 'Rolling back'
ROLLBACK TRAN;
SELECT @@ROWCOUNT AS NEWROWCOUNT;
END
RAISERROR ('Failure Message %s ROWCOUNT %d',16,1, @ErrMsg, @rc);
END CATCH
December 15, 2011 at 9:01 am
valeryk2000 (12/15/2011)
Thank you DA, TB & GS!The reason I asked is that in the past I had a problem with try/catch when parent proc did not catch the error in the internal one. By the way, did anybody have an experience in using @@RowCount within the Catch block?
@@RowCount is the number of rows operated on by the last line of code. Since that was probably the Raiserror command, or something like that, which doesn't operate on rows, it won't work. You need to capture the value before you exit to the Catch block.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 15, 2011 at 9:14 am
Furthermore, do some really serious testing if you intend to put a transaction in the calling procedure surrounding the call(s) to the subprocedure(s). Errors raised in the sub procedure(s), but not properly handled in the sub procedure's scope will doom your calling procedure's transaction. With this in mind have a good look at the setting "xact_abort". This heavily influences the behavior of the try-catch statement and the transaction in error conditions.
In short, a sub-procedure that is called in an enclosing transaction should:
a: explicitly set xact_abort off (for example: the default for xact_abort is "on" when called from SSMS, but it is "off" when executed by service broker),
b: save the transaction,
c: use a try-catch block that rolls the transaction back to the save point if an error occurs,
d: raise a new error that functionally explains what went wrong.
An example to play with:
use tempdb
go
-- A (sub) procedure that is intended to be called inside an
-- enclosing transaction. It saves the transaction state
-- and deals with any errors it may cause, but it does not ever
-- commit or rollback the transaction that it didn't start to
-- avoid error 266: "Transaction count after EXECUTE indicates a
-- mismatching number of BEGIN and COMMIT statements. Previous
-- count = n, current count = m."
create procedure dbo.subproc
as
begin
set xact_abort off; -- let us deal with errors ourselves.
create table #tbl (
id int not null,
primary key (id)
);
-- Save the transaction state for if we run into an error
-- inside the try-block.
save transaction savepoint;
begin try
-- Insert 2 id's "1" to create a primary key violation error.
insert #tbl(id)
select 1
union all select 1
-- No commit here, the transaction is not ours to commit!
-- (error 266: Transaction count after Execute indicates a mismatching ...)
end try
begin catch
-- Only if the transaction is still valid...
if xact_state() > 0
-- do rollback to the save point;
rollback tran savepoint;
-- Else: If the transaction was rolled back or doomed
-- by the engine due to the error we can not do anything
-- to save the day. Simply leave the situation as it is
-- for the caller to deal with it.
-- Signal the problem to the caller by raising an error
-- high enough to be caught in it's catch block, but not
-- as high to abort the connection. 2 is merely an indication
-- of the location that it occured in,
raiserror( 'Failed to insert 2 IDs.', 16, 2);
end catch
return 0;
end
go
-- A test procedure that begins a transaction, then calls
-- a sub procedure. This procedure should be able to handle any
-- kind of error and leave your database in a consistent state
-- whatever happens inside the sub proc. Try dropping the
-- dbo.subproc or altering it to generate other error conditions
-- to see what happens to the transaction state and even execution
-- inside this procedure.
create procedure dbo.testproc
as
begin
set xact_abort off; -- Let us handle the errors.
-- Start a transaction.
begin tran trn;
begin try
-- Have the sub procedure do it's task.
exec dbo.subproc;
---- Have a 2nd sub procedure do it's task.
--exec dbo.subproc2;
-- Only if all went fine, commit the transaction.
commit tran trn;
end try
begin catch
-- For this demo, show what the transaction and error state
-- is.
select case xact_state()
when 1 then 'inside open transaction'
when 0 then 'no active transaction'
when -1 then 'doomed transaction'
end as [transaction state],
error_message(),
error_number(),
error_procedure(),
error_line();
-- If the transaction is still valid, we do a rollback to
-- undo any effects left by sub procs that may have succeeded
-- before the failure occured.
if xact_state() > 0
rollback tran trn; -- Only a valid transaction has a name.
else if xact_state() < 0
rollback tran; -- a doomed transaction has no name, "rollback
-- tran trn" would raise a new error.
-- Depending on the "contract" you have with the caller of your
-- procedure you can return a non-zero return value here or
-- raise another error for the caller to catch. In this demo the
-- procedure always exits successfully with return code 0.
end catch
return 0;
end
go
-- try it!
exec dbo.testproc;
go
When you're done:
-- clean up after the experiments.
drop procedure dbo.testproc
go
drop procedure dbo.subproc
go
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply