Extract from : SQL Server Error Handling Workbench
http://www.simple-talk.com/sql/t-sql-programming/sql-server-error-handling-workbench/
--Catch Error:
--=============================================
BEGIN TRY
BEGIN TRAN
UPDATE Test SET Col1 = 1/0
COMMIT TRAN
END TRY
BEGIN CATCH
IF (XACT_STATE()) = -1
BEGIN
ROLLBACK TRAN;
RETURN ERROR_NUMBER();
END
ELSE IF (XACT_STATE()) = 1
BEGIN
--it now depends on the type of error or possibly the line number
--of the error
IF ERROR_NUMBER() = 8134
BEGIN
ROLLBACK TRAN;
RETURN ERROR_NUMBER();
END
ELSE
BEGIN
COMMIT TRAN;
RETURN ERROR_NUMBER();
END
END
END CATCH
-- Retry if error occurs:
--=============================================
DECLARE @retry AS tinyint,
@retrymax AS tinyint,
@retrycount AS tinyint;
SET @retrycount = 0;
SET @retrymax = 2;
SET @retry = 1;
WHILE @retry = 1 AND @retrycount <= @retrymax
BEGIN
SET @retry = 0;
BEGIN TRY
UPDATE HumanResources.Employee
SET ContactID = ContactID
WHERE EmployeeID = 100;
END TRY
BEGIN CATCH
IF (ERROR_NUMBER() = 1205)
BEGIN
SET @retrycount = @retrycount + 1;
SET @retry = 1;
END
END CATCH
END