SQL Server:错误处理及事务控制

目录：

解读错误信息

RAISERROR

THROW

实例

    使用 @@ERROR

    使用 XACT_ABORT

    使用TRY/CATCH

现实中的事务语句

    删除

    更新

    银行取钱

 

 

解读错误信息

Msg 547, Level 16, State 0, Line 11
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Products_Categories".
The conflict occurred in database "TSQL2012", table "Production.Categories", column 'categoryid'.

Error number 

  ● SQL  Server 错误信息的编号从1~49999

  ● 自定义错误信息从50001开始

  ● 错误编号50000是为没有错误编号的自定义信息准备的。

Severity  level

SQL Server 一共26个严重级别  0~25。

  ● 严重级别>= 16的会记录SQL Server日志和Windows 应用程序日志

  ● 严重级别19~25 只能由 sysadmin觉得的成员处理

  ● 严重级别20~25被认为是致命错误。 会中断终端连接并回滚所有打开的事务。

  ● 严重级别0~10只是提示信息。

State  int 类型，最大值127， MS internal purposes

Error message  支持255个Unicode 字符

  ●  SQL  Server 错误信息都在  sys.messages里面

  ●  可以用sp_addmessage 添加自定义错误信息

 

RAISERROR（不会中断事务）

简单的传递信息可以使用级别0~9 。

如果你有sysadmin的角色，可以使用WITH LOG选项并设置一个严重级别>20的错误。error 发生的时候SQL Server会中断连接。

使用NOWAIT选项可以直接发送信息，而不用等大赛buffer

RAISERROR ('Error in usp_InsertCategories stored procedure', 16, 0);

-- Formatting the RAISERROR string
RAISERROR ('Error in % stored procedure', 16, 0, N'usp_InsertCategories');

-- In addition, you can use a variable: 
GO
DECLARE @message AS NVARCHAR(1000) = N'Error in % stored procedure';
RAISERROR (@message, 16, 0, N'usp_InsertCategories');

-- And you can add the formatting outside RAISERROR using the FORMATMESSAGE function:
GO
DECLARE @message AS NVARCHAR(1000) = N'Error in % stored procedure';
SELECT @message = FORMATMESSAGE (@message, N'usp_InsertCategories');
RAISERROR (@message, 16, 0);

THROW （会中断事务）

-- You can issue a simple THROW as follows:
THROW 50000, 'Error in usp_InsertCategories stored procedure', 0;

-- Because THROW does not allow formatting of the message parameter, you can use FORMATMESSAGE()
GO
DECLARE @message AS NVARCHAR(1000) = N'Error in % stored procedure';
SELECT @message = FORMATMESSAGE (@message, N'usp_InsertCategories');
THROW 50000, @message, 0;

 

-- RAISERROR does not normally terminate a batch:
RAISERROR ('Hi there', 16, 0);
PRINT 'RAISERROR error'; -- Prints
GO

-- However, THROW does terminate the batch:
THROW 50000, 'Hi there', 0;
PRINT 'THROW error'; -- Does not print
GO

 

实例

使用 @@ERROR

DECLARE @errnum AS int;
BEGIN TRAN;
SET IDENTITY_INSERT Production.Products ON;
INSERT INTO Production.Products(productid, productname, supplierid, categoryid, unitprice, discontinued)
    VALUES(1, N'Test1: Ok categoryid', 1, 1, 18.00, 0);
SET @errnum = @@ERROR; 
IF @errnum <> 0 -- Handle the error
    BEGIN 
        PRINT 'Insert into Production.Products failed with error ' + CAST(@errnum AS VARCHAR);
    END

DECLARE @errnum AS int;
BEGIN TRAN;
    SET IDENTITY_INSERT Production.Products ON;
    -- Insert #1 will fail because of duplicate primary key
    INSERT INTO Production.Products(productid, productname, supplierid, categoryid,     unitprice, discontinued)
        VALUES(1, N'Test1: Ok categoryid', 1, 1, 18.00, 0);
    SET @errnum = @@ERROR;
    IF @errnum <> 0
        BEGIN 
            IF @@TRANCOUNT > 0 ROLLBACK TRAN;
            PRINT 'Insert #1 into Production.Products failed with error ' + CAST(@errnum AS VARCHAR);
        END; 
    -- Insert #2 will succeed
    INSERT INTO Production.Products(productid, productname, supplierid, categoryid,     unitprice, discontinued)
        VALUES(101, N'Test2: Bad categoryid', 1, 1, 18.00, 0);
    SET @errnum = @@ERROR;
    IF @errnum <> 0
        BEGIN 
            IF @@TRANCOUNT > 0 ROLLBACK TRAN;
            PRINT 'Insert #2 into Production.Products failed with error ' + CAST(@errnum AS VARCHAR);
        END; 
    SET IDENTITY_INSERT Production.Products OFF;
    IF @@TRANCOUNT > 0 COMMIT TRAN;
-- Remove the inserted row
DELETE FROM Production.Products WHERE productid = 101;
PRINT 'Deleted ' + CAST(@@ROWCOUNT AS VARCHAR) + ' rows';

使用 XACT_ABORT

使用XACT_ABORT，语句中发生错误，整段语句都会中止。

SET XACT_ABORT ON;
PRINT 'Before error';
SET IDENTITY_INSERT Production.Products ON;
INSERT INTO Production.Products(productid, productname, supplierid, categoryid, unitprice, discontinued)
    VALUES(1, N'Test1: Ok categoryid', 1, 1, 18.00, 0);
SET IDENTITY_INSERT Production.Products OFF;
PRINT 'After error';
GO
PRINT 'New batch';
SET XACT_ABORT OFF;

-- Using THROW with XACT_ABORT. 
USE TSQL2012;
GO
SET XACT_ABORT ON;
PRINT 'Before error';
THROW 50000, 'Error in usp_InsertCategories stored procedure', 0;
PRINT 'After error';
GO
PRINT 'New batch';
SET XACT_ABORT OFF;

@@ERROR第二个例子中使用XACT_ABORT以后，第二条语句这回就无效了。

DECLARE @errnum AS int;
SET XACT_ABORT ON; 
BEGIN TRAN;
    SET IDENTITY_INSERT Production.Products ON;
    -- Insert #1 will fail because of duplicate primary key
    INSERT INTO Production.Products(productid, productname, supplierid, categoryid,     unitprice, discontinued)
        VALUES(1, N'Test1: Ok categoryid', 1, 1, 18.00, 0);
    SET @errnum = @@ERROR;
    IF @errnum <> 0
        BEGIN 
            IF @@TRANCOUNT > 0 ROLLBACK TRAN;
            PRINT 'Error in first INSERT';
        END; 
    -- Insert #2 no longer succeeds
    INSERT INTO Production.Products(productid, productname, supplierid, categoryid,     unitprice, discontinued)
        VALUES(101, N'Test2: Bad categoryid', 1, 1, 18.00, 0);
    SET @errnum = @@ERROR;
    IF @errnum <> 0
        BEGIN 
            -- Take actions based on the error
            IF @@TRANCOUNT > 0 ROLLBACK TRAN;
            PRINT 'Error in second INSERT';
        END; 
    SET IDENTITY_INSERT Production.Products OFF;
    IF @@TRANCOUNT > 0 COMMIT TRAN;
GO
 
DELETE FROM Production.Products WHERE productid = 101;
PRINT 'Deleted ' + CAST(@@ROWCOUNT AS VARCHAR) + ' rows'; 
SET XACT_ABORT OFF;
GO
SELECT XACT_STATE(), @@TRANCOUNT;

使用TRY/CATCH

格式

--Transactions extend batches
BEGIN TRY
 BEGIN TRANSACTION 
  INSERT INTO Sales.SalesOrderHeader... --Succeeds
  INSERT INTO Sales.SalesOrderDetail... --Fails
 COMMIT TRANSACTION -- If no errors, transaction completes
END TRY
BEGIN CATCH
 --Inserted rows still exist in Sales.SalesOrderHeader SELECT ERROR_NUMBER()
 ROLLBACK TRANSACTION --Any transaction work undone
END CATCH;

BEGIN TRY
BEGIN TRAN;
    SET IDENTITY_INSERT Production.Products ON;
    INSERT INTO Production.Products(productid, productname, supplierid, categoryid, unitprice, discontinued)
        VALUES(1, N'Test1: Ok categoryid', 1, 1, 18.00, 0);
    INSERT INTO Production.Products(productid, productname, supplierid, categoryid, unitprice, discontinued)
        VALUES(101, N'Test2: Bad categoryid', 1, 10, 18.00, 0);
    SET IDENTITY_INSERT Production.Products OFF;
COMMIT TRAN;
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() = 2627 -- Duplicate key violation
        BEGIN
            PRINT 'Primary Key violation';
        END
    ELSE IF ERROR_NUMBER() = 547 -- Constraint violations
        BEGIN
            PRINT 'Constraint violation';
        END
    ELSE
        BEGIN
            PRINT 'Unhandled error';
        END;
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
END CATCH;

-- revise the CATCH block using variables to capture error information and re-raise the error using RAISERROR. 
USE TSQL2012;
GO
SET NOCOUNT ON;
DECLARE @error_number AS INT, @error_message AS NVARCHAR(1000), @error_severity AS INT;
BEGIN TRY
BEGIN TRAN;
    SET IDENTITY_INSERT Production.Products ON;
    INSERT INTO Production.Products(productid, productname, supplierid, categoryid,         unitprice, discontinued)
        VALUES(1, N'Test1: Ok categoryid', 1, 1, 18.00, 0);
    INSERT INTO Production.Products(productid, productname, supplierid, categoryid,         unitprice, discontinued)
        VALUES(101, N'Test2: Bad categoryid', 1, 10, 18.00, 0);
    SET IDENTITY_INSERT Production.Products OFF;
    COMMIT TRAN;
END TRY
BEGIN CATCH
    SELECT XACT_STATE() as 'XACT_STATE', @@TRANCOUNT as '@@TRANCOUNT';
    SELECT @error_number = ERROR_NUMBER(), @error_message = ERROR_MESSAGE(), @error_severity = ERROR_SEVERITY();
    RAISERROR (@error_message, @error_severity, 1);
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
END CATCH;

-- use a THROW statement without parameters re-raise (re-throw) the original error message and send it back to the client. 
USE TSQL2012;
GO
BEGIN TRY
BEGIN TRAN;
    SET IDENTITY_INSERT Production.Products ON;
    INSERT INTO Production.Products(productid, productname, supplierid, categoryid,         unitprice, discontinued)
        VALUES(1, N'Test1: Ok categoryid', 1, 1, 18.00, 0);
    INSERT INTO Production.Products(productid, productname, supplierid, categoryid,         unitprice, discontinued)
        VALUES(101, N'Test2: Bad categoryid', 1, 10, 18.00, 0);
    SET IDENTITY_INSERT Production.Products OFF;
COMMIT TRAN;
END TRY
BEGIN CATCH
    SELECT XACT_STATE() as 'XACT_STATE', @@TRANCOUNT as '@@TRANCOUNT';
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH;
GO
SELECT XACT_STATE() as 'XACT_STATE', @@TRANCOUNT as '@@TRANCOUNT';

 

现实中的事务语句

删除

--删除
CREATE PROCEDURE [dbo].[Students_Delete](@ID int)
WITH EXECUTE AS CALLER
AS
BEGIN
    --Check to make sure the ID does exist
    --If not does, return error
    DECLARE @existing AS int = 0
    SELECT @existing = count(ID)  
    FROM Students
    WHERE ID = @ID
    
    IF @existing <> 1
    BEGIN
        RAISERROR ('ID does not exist', 1, 1)
        RETURN 0
    END
        --Attempt Delete
        DELETE FROM [dbo].[Students]
        WHERE ID = @ID
     
        --check to see if update occured 
        --and return status
        IF @@ROWCOUNT = 1
            BEGIN
                INSERT INTO StudentDeleteLog 
                VALUES (suser_sname(), @ID, getdate())
                RETURN 1
            END
            
        ELSE 
            RETURN 0
END
GO

更新

CREATE PROCEDURE [dbo].[Students_Update]
(    @ID int,
        @LASTNAME varchar(50),
        @FIRSTNAME varchar(50),
        @STATE varchar(50),
        @PHONE varchar(50),
        @EMAIL varchar(50),
    @GRADYEAR int,
       @GPA decimal(20,10),
    @PROGRAM varchar(50),
    @NEWSLETTER bit
)
AS
BEGIN
    --Check to make sure the ID does exist
    --If not does, return error
    DECLARE @existing AS int = 0
    SELECT @existing = count(ID)  
    FROM Students
    WHERE ID = @ID
    
    IF @existing <> 1
    BEGIN
        RAISERROR ('ID does not exist', 1, 1)
        RETURN 0
    END
    --Can not subscribe to newsletter if email is null
    IF (@email IS NULL)
        SET @NEWSLETTER = 0
 
    --Attempt Update
UPDATE [dbo].[Students]
   SET [LASTNAME] = @LASTNAME 
      ,[FIRSTNAME] = @FIRSTNAME 
      ,[STATE] = @STATE 
      ,[PHONE] = @PHONE 
      ,[EMAIL] = @EMAIL 
      ,[GRADYEAR] = @GRADYEAR 
      ,[GPA] = @GPA 
      ,[PROGRAM] = @PROGRAM 
      ,[NEWSLETTER] = @NEWSLETTER 
 WHERE ID = @ID
     
           --check to see if update occured 
           --and return status
           IF @@ROWCOUNT = 1
                RETURN 1
           ELSE 
                RETURN 0
END
GO

银行取钱

BEGIN TRAN;
    IF NOT EXISTS (
        SELECT * FROM Accounts WITH(UPDLOCK)  --只有当前的事务可以查看
        WHERE AccountID = 47387438 AND Balance >= 400
    )
    BEGIN
        ROOLBACK TRAN;
        THROW 50000,'Tobias is too poor',1;
    END
    UPDATE Accounts SET
        Balance -=400
    WHERE AccountID = 47387438;
COMMIT TRAN;

--银行取钱高效版本
BEGIN TRAN;
    UPDATE Accounts SET
        Balance -= 400
    WHERE AccountID = 47387438 AND Balance >= 400
    IF(@@ROWCOUNT <> 1)
    BEGIN
        ROLLBACK TRAN;
        THROW 50000,'Tobias is too poor ',1;
    END
COMMIT TRAN;

 

参考文档

Database Engine Error Severities

https://msdn.microsoft.com/en-us/library/ms164086.aspx

SET XACT_ABORT (Transact-SQL)

https://msdn.microsoft.com/zh-tw/library/ms188792.aspx