SQL 2008r2, I am trying to use best practice; using the sql error system and adding my custom errors.
Where is sys.messages table, containing all the error messages in the SSMS directory tree? Or is an sproc the only way to see them; SELECT * FROM sys.messages WHERE language_id = 1033
RAISERROR (Transact-SQL) https://msdn.microsoft.com/en-us/library/ms178592.aspx
see B. Creating an ad hoc message in sys.messages
I do not think I should create and drop messages in every SPROC as the ms example above suggests. I think I should add them to sys.message with @msgnum 50001 or higher, in one place, let them persist, and then use them over and over again.
The only way I see to do this; is an SPROC with each custom message added, sp_addmessage... if it does not exisit. Run this sproc every time the sqlserver is run or when a new message is added. Unless the messages already persist then only when a new message is added?
I think my ideas are wrong but I did not know how to ask, hopefully this describes my question.
What is the proper way to create and maintain custom error messages in SQL 2008r2?