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.

Based on my previous post on database mail, Khaley writes to ask a question.

"I am using your example for using sp_send_dbmail to send query results as an attachment. However, the query results are too big. "File attachment or query results size exceeds allowable value of 1000000 bytes."

Is there a way to increase the deafult max of the query results?"

With a little research I was able to find the following.  Surprisingly, I couldn't find this in BOL, though.  Here is my reply to Khaley.

That's a good question, one I should blog about.  Yes, you can change these params, but as you might expect BOL is silent on the matter.  If you navigate to Management | Database Mail in object explorer, and right click Database Mail, you can select Configure Database Mail.  Click Next on the opening screen.  On the following screen, you can select View or Change System parameters.  On the next screen you will see an option for Maximum File Size, which you can change by clicking and overwriting. 

Again, thanks for the question.  Good Blog-fodder!

Jon

I was at a customer the other day, and something interesting came up.  The server was SQL Server 2005 - an in place upgrade from 2000 - and we were troubleshooting a performance problem.  Of course, I wanted to select from some DMVs to see what was ailing the SQL Server; however, an error returned stating it could not find an object.  I was sure I knew the name of it, so I was a little confused.  We started loooking at the database options, and found that master was still in 8.0 compatibility mode.  All the other databases were 9.0 compatibility, including MSDB.  It was strange that master was not converted, as well. 

We were able to change the compatibility mode to 9.0 and as you might expect, the functionality we expected, returned.  This is definitely something to watch out for in future upgrades, though.

Jon