Use explicit transactions and Try/Catch in your Stored procedures. Combine that with whatever error handling is available and efficient in your client development language/tool. I never Try to hide errors in the db layer, i want them to be thrown from db and handled in the client app, but with explicit transactions and Try/Catch in Sql server, i can keep the db consistent.
I would like to suggest you take a look at [Error Handling in SQL 2005 and above from Erland Sommarskog]. Erland has written alot of in depth articles, but the one I mention is brilliant. He provides examples and explains the limitations of each possibility. I am hoping he refreshes the article when the next version of SQL Server is released, incorporating even better error handling. :