May 28, 2011 at 12:34 pm
Hi all,
I know that Try/Catch was supposed to make our lives easier, but I am still perplexed by the right way error handling should be used in procs. Can someone please help me to understand when to use which method? Try/Catch, @rc, RAISERROR, RETURN...or combination of some or all of these?
Suppose I want this parent proc to fail when child proc fails:
BEGIN TRY
exec usp_childproc_fails
END TRY
BEGIN CATCH
RAISERROR or RETURN or set @rc???
END CATCH
Now, should the child proc be designed to exit/RETURN with an actual result code, to be captured by main proc with @rc? I've read that the use of RETURN is replaced with the Try/Catch construct. OK, so if true, then does this mean both the child proc and parent procs are both written with try/catch constructs? I am finding that when I know the child proc fails, the main proc will exit successfully - what I don't want. This is where is seems that I should use RETURN @rc, but I've read that this is the older way of handling. Yet, the only way I can get the above to truly fail is to combine methods:
using the example above:
child proc rewritten to RETURN non-zero
BEGIN TRY
exec @rc = usp_childproc_fails
END TRY
BEGIN CATCH
@rc or RAISERROR or RETURN???
END CATCH
Is it me, or were the days of only capturing @@error so much easier? Please help me to understand why I can't see the benefits of the new options - I spend more time testing my incorrect error handling in my code than writing the code itself.
Thank you,
Sharon
May 28, 2011 at 2:01 pm
Uing @@ERROR usually requires a GOTO and/or lots of IF statements.
Try ... Catch makes the code easier to maintain.
As far as nested Try Catch an example of a Grandparent, Parent & Child Try Catch is contained in the following article:
This article may be of help as well:
http://www.4guysfromrolla.com/webtech/041906-1.shtml
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 29, 2011 at 1:04 pm
Thank you for the articles. I've read them, and the one thing I am wondering is...are try/catch constructs best used for when only doing DML? I have been trying to update legacy code, most of which contains stored procedures that I often caught with the exec @rc = usp_storedproc method. I'm also baffled with times I need to us sp_executesql or xp_cmdshell. It seems to me that I'm supposed to remove the @rc in favor of the try/catch from what I've read, but where I intend to have the main proc fail, it just continues on. It seemd like more wok to do it this way, but is this a correct approach?
BEGIN TRY
EXEC @rc = master..xp_cmdshell @sql
IF @rc <> 0 EXEC dbo.usp_ErrorHandler --this proc contains a RAISERROR
EXEC @rc dbo.usp_checkfileexists @fullattachfilename, @result output --this proc does not contain a RAISERROR
IF (@rc <> 1)
begin
RAISERROR ('File %s was not created. Failing...', 16,1,@fullattachfilename)
end
END TRY
BEGIN CATCH
RAISERROR ('ERROR creating email attachment. Failing...', 16,1,@fullattachfilename)
END CATCH
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply