best practices for catching exceptions from sql in c#

Question

What are some good ways to catch business logic exceptions or return values from SQL in c#? For instance upon creating a new user, if the user already exists, the system, and the user must be notified. I have used the raise_error() method with a particular state int value, I have used stored procedure return int values, and I have also selected a msg column with a particular structure. Are there any best practices for this?

Answer 1

i always do two things:

1. use raiseerror, which manifests on the code side as an exception, and
2. return an error code

this lets me catch errors both in C# code and also when one stored procedure calls another

[sql server now supports a try-catch block, but i haven't used it yet]