Home » Tip Bank

Language: Enterprise
Expertise: Advanced

Nov 30, 2001

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.