Most likely

every DBA reading this article already knows that SQL Server 2005 has many new

features that include, the new error handler. Everyone who is working with SQL Server 2000 knows that T-SQL's ability to handle errors is limited and cumbersome. SQL Server 2005 allows one to handle errors much more elegantly and much more limitless

There are bunch of articles about error handlers for SQL Server 2005.

We all know that one basic error in writing a code is not to have error handling in the code. No matter how simple the process may be, the code should recover or bail out of any errors that occur.

By "handle error" we try to anticipate things that might not work as expected. In this

case we would like to write an exception, notify the user and find the cause of

the error. This means that the logging mechanism should be

incorporated in mostly all T-SQL codes. The error handling code should save

the logged error data for proper debugging.

This article is not about an explanation of SQL Server 2005 error handler feature; Instead it shows the examples and explanations of how to implement the standard error handler mechanism for handling the errors in SQL Server 2005. As a DBA, I prefer using the standardized procedure template for the entire company. This is why I created the stored procedure templates for transactional and non transactional procedures. In an ideal world error handling must be simple, incomplete transactions must never be committed and any uncommitted or non rollback transactions should never be left. Stored procedure should not rollback a

transaction that was started by a parent procedure (caller). 

Let’s see how it was done. This article is going to present two stored procedure templates. They handle non transactional and transactional procedures that satisfy the basic conditions for error handing outlined above.

At the beginning, I created error log table to store error and system data.

CREATE TABLE ERROR_LOG 
( [ERROR_LOG_ID] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY ,
  [ERROR_LOG_PROGRAM_NM] [varchar] (128) NULL , -- ERROR_PROCEDURE()
  [ERROR_LINE_NO] INT NULL,                     -- ERROR_LINE()
  [ERROR_LOG_ERROR_NO] [int] NULL ,             -- ERROR_NUMBER()
  [ERROR_LOG_ERROR_DSC] [varchar] (4000) NULL , -- ERROR_MESSAGE()
  [ERROR_SEVERITY_NO] INT NULL,                 -- ERROR_SEVERITY()
  [ERROR_STATE_NO] INT NULL,                    -- ERROR_STATE()
  [ERROR_LOG_PROGRAM_SECTION_NM] [varchar] (255) NULL , 
  [ERROR_LOG_SPID_NO] [int] NULL ,  
  [ERROR_LOG_EVENT] [varchar] (255) NULL ,
  [ERROR_LOG_PARAMETER] [int] NULL ,
  [ERROR_LOG_EVENT_INFO] [varchar] (1000) NULL , 
  [ERROR_DB_NAME] [varchar] (50) NULL ,
  [CREATEDATE] [DATETIME] NOT NULL , 
  [CREATEUSERNAME] [VARCHAR] (128) NOT NULL ,
  [CREATEMACHINENAME] [VARCHAR] (128) NULL , 
  [CREATESOURCE] [VARCHAR] (128) NULL )
ALTER TABLE ERROR_LOG ADD
  CONSTRAINT [CURRDT] DEFAULT (getdate()) FOR [CreateDate],
  CONSTRAINT [CURRUSER] DEFAULT (suser_sname()) FOR [CreateUserName],
  CONSTRAINT [HOSTNM] DEFAULT (host_name()) FOR [CreateMachineName],
  CONSTRAINT [PROCESSNM] DEFAULT (@@procid) FOR [CreateSource]

The following system functions to acquire information about

errors within the CATCH block. The following functions, if called outside the CATCH

block, will return NULL.

ERROR_LINE() returns the line number at which the error occurred.

ERROR_MESSAGE() returns the text of the message that would

be returned to the application. The text includes the values supplied for any

substitutable parameters, such as lengths, object names, or times.

ERROR_NUMBER() returns the error number.

ERROR_PROCEDURE() returns the name of the stored procedure

or trigger in which the error occurred. This function returns NULL if the error

did not occur inside a stored procedure or trigger.

ERROR_SEVERITY() returns the severity.

ERROR_STATE() returns the state.

Then I created a standard stored procedure that will load an error tracked information to the table ERROR_LOG. The stored procedure takes information provided by the code, grabs and stores the information the last statement sent from a client to Microsoft® SQL Server by utilizing  DBCC INPUTBUFFER command. It gives the ability to store the set of parameters to the stored procedure where error has happened. In many cases it helped me debug a stored procedure and find the very specific error, which appeared only with specific set of input parameter’s.

SET QUOTED_IDENTIFIER OFF
GO
create procedure ERROR_LOG_2005
    @ERROR_LOG_PROGRAM_NM varchar(128) = NULL,
 @ERROR_LOG_PROGRAM_SECTION_NM varchar(255) = NULL,
    @ERROR_LOG_ERROR_NO int=null,
 @ERROR_LOG_ERROR_DSC varchar(4000) = NULL,
    @ERROR_DB_NAME varchar(50)=NULL
as
BEGIN
declare@prog varchar(128),
@errno int, 
@errmsg varchar(4000),
@proc_section_nm varchar(255),
@cmd varchar(50),
  @errline int,
@errstate int, 
@errseverity int,
@INSERTED_IDENTITY_ID int   -- Contains the ErrorLogID of the row inserted in the ERROR_LOG table.
declare@CreateUserName varchar(128),     -- last user changed the data 
@CreateMachineName varchar(128) , -- last machine changes-procedure were run from
@CreateSource varchar(128) -- last process that made a changes
SET NOCOUNT ON
-- Output parameter value of 0 indicates that error information was not logged
SET @INSERTED_IDENTITY_ID = 0;
-- for the logical/business error there is no system error
-- business error will be passed as parameter from stored procedure
IF (ISNULL(@ERROR_LOG_ERROR_NO,0) = 0)
 begin
set @ERROR_LOG_ERROR_NO = ERROR_NUMBER();
set @errseverity = ERROR_SEVERITY();
set @errstate = ERROR_STATE();
set @prog = ERROR_PROCEDURE();
set @errline = ERROR_LINE();
set @ERROR_LOG_ERROR_DSC = left(ltrim( IsNull(@ERROR_LOG_ERROR_DSC,'')) + ';' + ERROR_MESSAGE(), 4000);
 end
set @CreateUserName = CONVERT(sysname, CURRENT_USER);
set @CreateMachineName = host_name();
set @CreateSource = isnull(object_name(@@procid), '');
BEGIN TRY
-- Return if there is no error information to log
IF ( ISNULL(@ERROR_LOG_ERROR_NO,0) = 0 )
RETURN;
-- Return if inside an uncommittable transaction. 
-- Data insertion/modification is not allowed when a transaction is in an uncommittable state.
IF XACT_STATE() = -1
 BEGIN
set @errmsg = 'Cannot log error since the current transaction is in an uncommittable state. ' 
+ 'Rollback the transaction in order to successfully log error information.';
set @errmsg = 'Error in proc ' + isnull(@prog,' ') + ' ' + isnull(@errmsg,' ');
raiserror (@errmsg, 16, 1);
RETURN -1;
 END;
SET @cmd = "DBCC INPUTBUFFER( " + CAST(@@spid as varchar) + ")";
insert into ERROR_LOG  
(ERROR_LOG_EVENT, 
ERROR_LOG_PARAMETER,
ERROR_LOG_EVENT_INFO 
)
exec (@cmd);
set @INSERTED_IDENTITY_ID = IDENT_CURRENT('HIST_ERROR_LOG');
update dbo.ERROR_LOG
  SETCreateUserName = @CreateUserName,
CreateMachineName = @CreateMachineName,
CreateSource = @CreateSource,
ERROR_LOG_PROGRAM_NM = @ERROR_LOG_PROGRAM_NM,
ERROR_LOG_PROGRAM_SECTION_NM = @ERROR_LOG_PROGRAM_SECTION_NM,
ERROR_LOG_ERROR_NO = @ERROR_LOG_ERROR_NO,
ERROR_LOG_ERROR_DSC = @ERROR_LOG_ERROR_DSC,
ERROR_LOG_SPID_NO = @@SPID,
ERROR_DB_NAME = @ERROR_DB_NAME,
ERROR_LINE_NO = @errline,
ERROR_SEVERITY_NO = @errseverity,
ERROR_STATE_NO = @errstate
 WHERE ERROR_LOG_ID = @INSERTED_IDENTITY_ID
 END TRY
 BEGIN CATCH
set @errmsg = 'Error in proc ' + isnull(@prog,' ') + ' ' + isnull(@errmsg,' ');
raiserror(@errmsg, 16,1);
RETURN -1;
 END CATCH;
RETURN @ERROR_LOG_ERROR_NO;
End  -- End of stored procedure

Next step is the template creation.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON 
GO
CREATE procedure dbo.TEMPLATE_2005
as
begin
SET NOCOUNT ON

declare @$prog varchar(50), 
@$errno int, 
@$errmsg varchar(4000), 
@$proc_section_nm varchar(50),
@$row_cnt INT,
@$error_db_name varchar(50), 
@$CreateUserName varchar(128),   -- last user changed the data 
@$CreateMachineName varchar(128) -- last machine changes-procedure were run from
@$CreateSource varchar(128)-- last process that made a changes
select @$errno = NULL,  @$errmsg = NULL,  @$proc_section_nm = NULL
,  @$prog = LEFT(object_name(@@procid),50), @$row_cnt = NULL
, @$error_db_name = db_name();
--=========
BEGIN TRY
--=========
-- User Source code
--========
END TRY
--========
BEGIN CATCH
--===========
set @$errmsg = Left('Error ' +
CASE
WHEN @$errno > 0 THEN CAST(@$errno as varchar)
ELSE Cast(ERROR_NUMBER() as varchar)
END + 'in proc ' + isnull(@$prog,' ') + ' ' + 
CASE 
WHEN @$errno > 0 THEN isnull(@$errmsg,' ') 
ELSE isnull(@$errmsg,' ') + ISNULL(ERROR_MESSAGE(),'')
END ,4000);
raiserror (@$errmsg, 16, 1); 
EXEC dbo.ERROR_LOG_2005 @ERROR_LOG_PROGRAM_NM  = @$prog,  
@ERROR_LOG_PROGRAM_SECTION_NM  = @$proc_section_nm,
@ERROR_LOG_ERROR_NO  = @$errno,  
@ERROR_LOG_ERROR_DSC  = @$errmsg,
@ERROR_DB_NAME  = @$error_db_name
-- set the error if not set
IF (ISNULL(@$errno,0) = 0 )
set @$errno = ERROR_NUMBER();
END CATCH
SET NOCOUNT OFF; 
return @$errno;  
end

Here are some additional comments to the template. If you

would like to control precisely the statement or the sections where an error

happened for the purpose of easier debugging the code then the next statement should

be in front of the user defined section or before each SQL statement. For

example:

     set @$proc_section_nm = 'Section:  20';

The system can raise errors when it encounters a problem,

but we can also raise our own errors. The T-SQL command "RAISERROR"

returns an error code of our choice, along with a standard or custom level and

message. Our code can then check for these levels and messages and handle them

accordingly. In my opinion, error handler should not only handle the

system errors such as can’t UPDATE, INSERT, DELETE, SELECT and so on, but also handle the business' logical errors. For example, selection of zero rows is a perfectly valid result for the statement but can be considered as an error from a business prospective and may require handling like an inability

to retrieve the record.  So, if you would like to get the information on how many rows have been retrieved then inside a stored procedure the next statement should be placed as a first statement after

the SQL statement.

           set @$row_cnt = @@ROWCOUNT;

If we would like to capture a logical error then use  the next set of statements. This will send stored procedure to BEGIN CATCH block. For example:

 
Select user_no, user_id from
TABLE_PERSON where user_id = 5;
set @$row_cnt = @@ROWCOUNT;
IF (@$row_cnt = 0)
 BEGIN 
set @$errno = 100000;    -- Any user defined error number over 50000
set @$errmsg = 'Error message';
raiserror (@$errmsg, 16, 1);
 END

In addition, the template can have an application or company specific variables or parameters. For example, my template and error handler has inputted parameters for the processes because in our case each back end process has its own id. This is allows DBA to quickly find the error when process stored procedures are shared among the processes.

The template defined above is the simple template without the ability to run a group of transactional statements. Let’s see the differences between non transactional and transactional templates.

1. There is a check if procedure is inside of the parent transaction at the beginning of the stored procedure after the variable declaration.

Declare @$tran_flag;
set @$tran_flag = @@trancount;

2. The next statement should be placed as close to the beginning of data changes as possible. Some times I place the next statement right after the statement above when the stored procedure run time is short. But the statement can be moved and then it will change the place where the transaction is going to be started. The flag is the indicator that a transaction has started in this stored procedure when changes will be COMMITED or ROLLBACK at the end of the stored procedure. This is necessary when the statement is moved from the beginning of the procedure down.

if (@$tran_flag = 0)
 begin
begin transaction procmain_tran; 
set @$tran_started_flag = 'Y';
 end

3. COMMIT the transaction if no errors with next statement as the last statement before END TRY.

IF (@$tran_started_flag = 'Y')
 begin
COMMIT TRANSACTION ;
 end

4. ROLLBACK transaction inside of TRY CATCH – END CATCH block with next statement

IF (@$tran_started_flag = 'Y')
 begin
ROLLBACK TRANSACTION;
 end 
IF (@$tran_flag = 0)
 begin 
EXEC dbo.ERROR_LOG  @ERROR_LOG_PROGRAM_NM  = @$prog,
@ERROR_LOG_PROGRAM_SECTION_NM  = @$proc_section_nm,
@ERROR_LOG_ERROR_NO = @$errno,
@ERROR_LOG_ERROR_DSC  = @$errmsg,
@ERROR_DB_NAME  = @$error_db_name,
 End 

Example of a the transactional template is in file TRANS_TEMPLATE_2005.TXT

Conclusion

For sake of clarity, the template source code is simplified from the actual templates ,but all ideas are still remained. I have used these templates for a few months and I am still using them for SQL Server 2005 in development and production environment. It perfectly serves the need for all my application’s and back end processing stored procedures and it has been adopted as a standard for all SQL Server developers.