How do you handle errors in stored procedure?

You have lots of choices.  You can handle the error inside the stored procedure and then return an error code or message.  The way this is done varies depending on the database you are using.  Oracle, SQL Server, DB2, etc. each have a different way to handle errors.

I prefer not to have the database handle the error.  I would rather have the call to the database throw an exception and then catch the exception in the code and handle it there.  There are several reasons:

(1)  You still have to wrap your database call in a try/catch block.  After all, the database might be down, the connection string might be invalid, etc.  So you're going to be writing code to handle errors anyhow.

(2)  If you catch the error in your code, you can handle it the same way you handle other types of exceptions.

(3)  If you catch the error in your code, you can change databases without changing your error handling.