Jon Baker's Outer Join

So I finally had an opportunity to use Try...Catch in the real world, and I found the examples to be a little lacking around executing a stored proc.  Hopefully this will help.  If not, it will at least help me remeber when I forget it in a week.

In the old days we would trap for errors something like this.

declare @rc int
declare @msg varchar(3000)

exec @rc=usp_some_proc

if @rc <> 0 or @@Error <> 0
begin

   set @msg = 'Error in usp_some_proc'
   goto ERRORPROC

end

goto ENDPROC

ERRORPROC:

BEGIN
   RAISERROR (@msg, 16, 1)

ENDPROC:

Essentially we had to trap for errors in each proc individually, which was quite tedious.

In SQL Server 2005 we get a new error handling setup much like .NET languages using the Try...Catch construct.  Much has been made of this feature and how to use it and the other new error handling functions, so I won't repeat that here.  Essentially what I want to make note of is the ability of T-SQL to bubble up the errors to the calling procedure.  As I mentioned, this is _lightly_ documented.  Imagine the following

BEGIN TRY

   exec usp_some_proc1

   exec usp_some_proc2

   exec usp_some_proc3

END TRY

BEGIN CATCH

PRINT 'This error has been caught...'

PRINT 'Msg ' + cast(ERROR_NUMBER() as varchar) + ', Level ' + cast(ERROR_SEVERITY() as varchar) + ', State ' + cast(ERROR_STATE() as varchar) + ', Line ' + cast(ERROR_LINE() as varchar)

PRINT ERROR_MESSAGE()

END CATCH

Let's say all three procs catch and raise errors like the old method above.  When the error is thrown in child proc, the outer script will catch the error and drop to the catch block, thus bubbling it up the error, with little toil and trouble to you.  One interesting thing that is missing.  It is not possible to re-throw the error already thrown by the child.  You have to log another error or print out the results (unless I'm missing something...).  And finally, yeah, what about Finally?  Here's hoping we get that last piece of that construct in a service pack soon.  It's a really nice feature in .NET that I wish we had in T-SQL.

If you want further proof of bubbling, take a look at this example.  Thanks to Chip Downs for providing it.