Managing Custom Errors in SQL Server

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)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.

Share the Post:
data observability

Data Observability Explained

Data is the lifeblood of any successful business, as it is the driving force behind critical decision-making, insight generation, and strategic development. However, due to its intricate nature, ensuring the

Heading photo, Metadata.

What is Metadata?

What is metadata? Well, It’s an odd concept to wrap your head around. Metadata is essentially the secondary layer of data that tracks details about the “regular” data. The regular

XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved