Data validation includes both procedural and declarative data integrity and can both hurt and benefit performance.
Error handling using @@ERROR should typically be replaced by structured error handling using try/catch blocks.
Consider how you handle transactions in your try/catch blocks. If you need to commit or continue a transaction in a catch block, use the XACT_STATE() function to verify that the transaction is committable.
If many error messages are sent from stored procedures and triggers, consider creating the messages inside SQL Server using the sp_addmessage procedure instead of putting the actual error text in the stored procedure.
SQL Server messages support localization of messages into other languages; you don't need to create your own table structure to manage these texts.