Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

An Error Handling Template for 2005

By Leo Peysakhovich,

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
	  SET		CreateUserName = @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;
  1. 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
  1. 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
  1. 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.

Total article views: 17890 | Views in the last 30 days: 26
 
Related Articles
FORUM

Stored procedure error handler

Stored procedure error handler

FORUM

Stored Procedure with varchar(max) as parameter

Stored Procedure with varchar(max) as parameter

FORUM

Transaction Replication:Distributn Agent Error Could Not find stored procedure 'sp_MSdel_co'

Transaction Replication Error under distribution agent:could not find stored procedure 'sp_MSdel_co'...

FORUM

Error in Stored Procedure

Error in COALESCE stored Procedure

FORUM

Using Cursors Inside Stored procedure problem

creating cursor inside stored procedure gives error

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones