Often in many stored procedures, we will throw same error message by using RaiseError(). Instead we can register that error message using sp_addmessage and use the error message in all the stored procedures.

For example, often we will throw same type of error message, "ObjectName already exists", and "objectname" will change often. Here, we can register an error message like below and can use this error in all the occurances. This will gives better maintainance.

The message id supplied must be greater than 50,000.

USE master
GO
EXEC sp_addmessage
50002,
10,
N'The data %s already exists!'
GO


RAISERROR (50002, 10, 1, 'mytext')

Published under: SQL Server Tips ·  ·  ·  ·