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