Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Enterprise
Expertise: Advanced
Nov 30, 2001

Managing Custom Errors in SQL Server


Creating Custom Errors in SQL Server is not a hard thing to do, but managing those errors across multiple developers can be difficult. The question becomes: who has already used what custom error? This often leads to hours of wasted time. To make things easier, centralize all of your error messages in a stored procedure.

The first thing you need to do is create a stored procedure that will add the message to the sysmessages table. Because you will be calling this often, it needs to check to see if the message already exists and if not, add it.
 
CREATE procedure dbo.CreateCustomError
	(
	@ErrNumber	int,
	@ErrMessage	Varchar(2000)

As
SET NOCOUNT ON
Declare @ErrorNumber bigint

Set @ErrorNumber = dbo.vbObjectError(@ErrNumber);

if not exists (select Error from Master..sysmessages _
where error =
@ErrorNumber)
	exec dbo.sp_addmessage @msgnum = @ErrorNumber, _
@severity = 10,
		@msgtext = @ErrMessage,
		@with_log = 'false';
GO

Now, just create the following stored procedure to manage the addition of these messages for your developers.
 
CREATE procedure dbo.InitiateAllErrors

As
SET NOCOUNT ON
---------------------------Joe Smith Security Errors
6000-6299----------------------------------------

 exec dbo.CreateCustomError 6001, 'Security _
Failure - User Can not perform
action on specified on whatever';


---------------------------Bob Doe Errors
6300-6599----------------------------------------

 exec dbo.CreateCustomError 6300, 'Delete whatver _
Error - The whatever
already exists.';

GO

From Query Analyzer, just run the above stored procedure whenever new custom errors are added. You don't need to worry about one developer's custom errors getting written over another developer's custom error.
Thomas Messina
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date