Exception handling in T-SQL has always been rather lacking. Now, they have added try/catch support similar to that of .NET. This is typically used when you are using a transaction and performing multiple inserts, updates, or deletes. The following example shows the use of TRY / CATCH.
/* not exactly sure what this does yet, but it appears to be required */
SET XACT_ABORT ON
BEGIN TRY
BEGIN TRAN
/* update statement 1 */
/* update statement 2 */
END TRAN
END TRY
BEGIN CATCH TRAN_ABORT
DECLARE @ErrorNumber int
/* contains ErrorNumber for exception (i.e.: constraint violation, null violation, etc.) */
SET @ErrorNumber = @@error
/* do something based upon the @ErrorNumber */
END CATCH
This should make it easier to have more advanced T-SQL Stored Procedures that have proper exception handling.
Read the complete post at http://www.dotnettipoftheday.com/blog.aspx?id=177