question

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][1]. 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. [1]: http://www.sommarskog.se/error_handling_2005.html

I would also take a look at Alex Kuznetsov's Defensive Database Programming with SQL Server. [Amazon][1]'s not helpful for getting this; try [Red Gate][2] instead - after all, they published it! Incidentally, why does Red Gate feel the need to hide the books away from the main Store? Annoying... [1]: http://www.amazon.co.uk/Defensive-Database-Programming-SQL-Server/dp/190643445X [2]: http://www.red-gate.com/our-company/about/book-store/defensive-database-programming