best practices for catching exceptions from sql in c#

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?

From stackoverflow eulerfx

• 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]

  From Steven A. Lowe