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)AsSET NOCOUNT ONDeclare @ErrorNumber bigintSet @ErrorNumber = dbo.vbObjectError(@ErrNumber);if not exists (select Error from Master..sysmessages _where error [email protected])	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.InitiateAllErrorsAsSET NOCOUNT ON---------------------------Joe Smith Security Errors6000-6299---------------------------------------- exec dbo.CreateCustomError 6001, 'Security _Failure - User Can not performaction on specified on whatever';---------------------------Bob Doe Errors6300-6599---------------------------------------- exec dbo.CreateCustomError 6300, 'Delete whatver _Error - The whateveralready 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.