SQL Server 2005 error handling

来源：互联网发布：重庆时时彩做号软件编辑：程序博客网时间：2024/04/26 15:41

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