TSQL try catch blocks

TSQL in SQL 2k8 has lots of functionality that reminds me of excellent tools available in the .net world but not TSQL until now. Well, in some cases there was new functionality available in 2k5. One example is the "try catch" block.

BEGIN TRY

whatever sql statement you want to execute

END TRY

BEGIN CATCH

what to do when there is an error....

END CATCH


As with any type of application programming, error handling is often an overlooked "luxury" item in the eyes of everyone except the folks that have to debug when there is an error. As an application programmer that you be you! (and me). My advice is to create a generic table to hold error info when it happens. Something like this:

CREATE TABLE [dbo].[error_log](
[error_number] [varchar](500) NULL,
[error_message] [varchar](500) NULL,
[error_severity] [varchar](500) NULL,
[error_state] [varchar](500) NULL,
[error_procedure] [varchar](500) NULL,
[inserted] [datetime] default getdate() NULL,
[inserted_by] [varchar](500) default user_name() NULL,
[defactodate] [datetime] NULL,
[machine_name] [varchar](255) NULL
) ON [PRIMARY]

GO

Of course I wrap almost all of my statements in stored procedures, so you'll need a proc to perform the insert. Something like this:

create proc [dbo].[pr_ins_error_log]
@error_number varchar(500),
@error_message varchar(500),
@error_severity varchar(500),
@error_state varchar(500),
@error_procedure varchar(500),
@inserted_by varchar(500)
as
insert into error_log
([error_number],[error_message],[error_severity],[error_state],[error_procedure],inserted_by)
values
(@error_number,@error_message,@error_severity,@error_state,@error_procedure,@inserted_by)

GO

There are an array of excellent functions that will return tons of information on the error that just occured. A good example of this is error_number(). You can get creative with your own implementation but based on the objects created above, an example of how to use this would look something like this:

begin try

some generic insert statement

end try
begin catch

declare @user varchar(500) = user
exec error_logging.dbo.pr_ins_error_log error_number,error_message,error_severity,error_state,error_procedure,@user

end catch

Good luck with catching error and actual capturing them so that you have a set of "eyes" and there isn't as much guessing on what is really going wrong.