Handling a SQL Exceptions and Custom Error Messages

Hello guys,

I need some ideas on how to handle an exception or a user defined error message.

I have a procedure that creates a new user. Lets say if the e-mail address entered is already in use. What are some of the best practices for notifying the user that the e-mail address is already in use?

This is what I was thinking...

Solution #1
-----
My proc will raise an error with a message id that is great than 50000, then my DAL will recognize this is a user defined error and spit back to the user instead of trapping it.

Solution #2
-----
The proc should have an output param ( @.CreationStatus CHAR(1) ).
If the @.CreationStatus has a value for example "E", I will have lookup the value for "E" in my app and spit back that custom error message. I don't really like this option because it is too concrete.

What are some of the ways you deal with this situation?

Your suggestions are greatly appreciated.

Thank you!

You could return a @.status value with (0=success, 1= failure and an appropriate status message @.Statusmsg ( = 'Success' if @.status = 0, custom error message if @.status = 1)

From your application you could check the value in @.status and if its not 0, then display the message from @.statusmsg. You can handle this in a number of ways, It comes down to setting up one standard way of doing it across all procs and communicating with your team and documenting it so the same logic is followed across all procs.