I would like to be able to filter common error conditions generated within my stored procedures in SQL Server 2000 to help callers by generating more helpful error messages to pass back as OUTPUT parameters.
The kind of things I want to trap are Referential constraint violations and primary key violations and the like.
However, although I can detect the error number (@@ERROR), and cannot seem to find a way of detecting the post-processed message or the variable message information that is passed to the client, so that instead of just generating something like
"You cannot delete this personnel record because it is referenced elsewhere",
I could generate something like
"You cannot delete this personnel record because it is referenced in the ECardOrder table"
Can this information be retrieved or this there a better way?
The intention is to generate a message acceptable for the user to see or at least provide the means to the client to generate it.