如何在sqlserver 的函数或存储过程中抛出异常

妖狐艹你老母 2021-07-04 21:49 361阅读 0赞

`raiserror` 的作用： `raiserror` 是用于抛出一个错误。\[ 以下资料来源于sql server 2005的帮助 \]

其语法如下：

RAISERROR ( { msg_id | msg_str | @local_variable }        
                { ,severity ,state }        
                [ ,argument [ ,...n ] ] 
              )       
       [ WITH option [ ,...n ] ]

简要说明一下：

**第一个参数：`{ msg_id | msg_str | @local_variable }`**  
`msg_id`：表示可以是一个`sys.messages`表中定义的消息代号；  
使用 `sp_addmessage` 存储在 `sys.messages` 目录视图中的用户定义错误消息号。  
用户定义错误消息的错误号应当大于 `50000`。

`msg_str`：表示也可以是一个用户定义消息，该错误消息最长可以有 2047 个字符；  
（如果是常量，请使用N’xxxx’，因为是`nvarchar`的）  
当指定 `msg_str` 时，`RAISERROR` 将引发一个错误号为 `5000` 的错误消息。

`@local_variable`：表示也可以是按照 `msg_str` 方式的格式化字符串变量。

**第二个参数：`severity`**  
用户定义的与该消息关联的严重级别。（这个很重要）  
任何用户都可以指定 0 到 18 之间的严重级别。  
`[0,10]`的闭区间内，不会跳到`catch`；  
如果是\[11,19\],则跳到`catch`；  
如果\[20,无穷)，则直接终止数据库连接；

**第三个参数：`state`**  
如果在多个位置引发相同的用户定义错误，  
则针对每个位置使用唯一的状态号有助于找到引发错误的代码段。

介于 1 至 127 之间的任意整数。（state 默认值为1）
            当state 值为 0 或大于 127 时会生成错误！

**第四个参数：`argument`**  
用于代替 `msg_str` 或对应于 `msg_id` 的消息中的定义的变量的参数。

**第五个参数：`option`**  
错误的自定义选项，可以是下表中的任一值：  
`LOG` ：在错误日志和应用程序日志中记录错误；  
`NOWAIT`：将消息立即发送给客户端；  
`SETERROR`：将 `@@ERROR` 值和 `ERROR_NUMBER` 值设置为 `msg_id` 或 `50000`；

代码示例

--示例1
    DECLARE @raiseErrorCode nvarchar(50)
    SET @raiseErrorCode = CONVERT(nvarchar(50), YOUR UNIQUEIDENTIFIER KEY)
    RAISERROR('%s INVALID ID. There is no record in table',16,1, @raiseErrorCode)

--示例2
    
    RAISERROR (
                 N'This is message %s %d.', -- Message text,
                 10,                        -- Severity,
                 1,                         -- State,
                 N'number',                 -- First argument.
                 5                          -- Second argument.
              ); 
    -- The message text returned is: This is message number 5.
    GO

--示例3
    
    RAISERROR (N'<<%*.*s>>', -- Message text.
               10,           -- Severity,
               1,            -- State,
               7,            -- First argument used for width.
               3,            -- Second argument used for precision.
               N'abcde');    -- Third argument supplies the string.
    -- The message text returned is: << abc>>.
    GO

--示例4
    RAISERROR (N'<<%7.3s>>', -- Message text.
               10,           -- Severity,
               1,            -- State,
               N'abcde');    -- First argument supplies the string.
    -- The message text returned is: << abc>>.
    GO

--示例5 
    --A. 从 CATCH 块返回错误消息
    --以下代码示例显示如何在 TRY 块中使用 RAISERROR 使执行跳至关联的 CATCH 块中。
    --它还显示如何使用 RAISERROR 返回有关调用 CATCH 块的错误的信息。
    
    BEGIN TRY
        RAISERROR ('Error raised in TRY block.', -- Message text.
                    16, -- Severity.
                    1 -- State.
                   );
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;
    
        SELECT 
            @ErrorMessage = ERROR_MESSAGE(),
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE();
    
        RAISERROR (@ErrorMessage,  -- Message text.
                   @ErrorSeverity, -- Severity.
                   @ErrorState     -- State.
                   );
    END CATCH;

--示例6
    --B. 在 sys.messages 中创建即席消息
    --以下示例显示如何引发 sys.messages 目录视图中存储的消息。
    --该消息通过 sp_addmessage 系统存储过程，以消息号50005添加到 sys.messages 目录视图中。
    
    sp_addmessage @msgnum = 50005,
                   @severity = 10,
                   @msgtext = N'<<%7.3s>>';
    GO
    
    RAISERROR (50005, -- Message id.
               10,    -- Severity,
               1,     -- State,
               N'abcde'); -- First argument supplies the string.
    -- The message text returned is: << abc>>.
    GO
    
    sp_dropmessage @msgnum = 50005;
    GO

--示例7
    --C. 使用局部变量提供消息文本
    --以下代码示例显示如何使用局部变量为 RAISERROR 语句提供消息文本。
    
    sp_addmessage @msgnum = 50005,
                  @severity = 10,
                  @msgtext = N'<<%7.3s>>';
    GO
    
    RAISERROR (50005, -- Message id.
               10,    -- Severity,
               1,     -- State,
               N'abcde'); -- First argument supplies the string.
    -- The message text returned is: << abc>>.
    GO
    
    sp_dropmessage @msgnum = 50005;
    GO

同时在存储过程中将事务和`try…catch`联合使用

在存储过程中使用事务时，如果没有`try…catch`语句，那么当`set xact_abort on`时，如果有错误发生，在批处理语句结束后，系统会自动回滚所有的sql操作。当`set xact_abort off`时，如果有错误发生，在批处理语句结束后，系统会执行所有没有发生错误的语句，发生错误的语句将不会被执行。

在存储过程中使用事务时，如果存在`try…catch`语句块，那么当捕获到错误时，需要在`catch`语句块中手动进行`Rollback`操作，否则系统会给客户端传递一条错误信息。如果在存储过程开始处将`set xact_abort on`，那么当有错误发生时，系统会将当前事务置为不可提交状态，即会将`xact_state()`置为`-1`，此时只可以对事务进行`Rollback`操作，不可进行提交(`commit`)操作，那么我们在`catch`语句块中就可以根据`xact_state()`的值来判断是否有事务处于不可提交状态，如果有则可以进行`rollback`操作了。如果在存储过程开始处将`set xact_abort off`，那么当有错误发生时，系统不会讲`xact_state()`置为`-1`，那么我们在`catch`块中就不可以根据该函数值来判断是否需要进行`rollback`了，但是我们可以根据`@@Trancount`全局变量来判断，如果在`catch`块中判断出`@@Trancount`数值大于`0`，代表还有未提交的事务，既然进入`catch`语句块了，那么还存在未提交的事务，该事务应该是需要`rollback`的，但是这种方法在某些情况下可能判断的不准确。推荐的方法还是将`set xact_abort on`，然后在`catch`中判断`xact_state()`的值来判断是否需要`Rollback`操作。

下面我们来看看两个例子：

## 一．使用`Set xact_abort on` ##

Create  proc  myProcedure
    
    As
        begin
           set xact_abort on;
           begin try
               begin tran
                  insert into TestStu values('Terry','boy',23);
                  insert into TestStu values('Mary','girl',21);
               commit tran
           end try
    
           begin catch
    
                -- 在此可以使用xact_state()来判断是否有不可提交的事务,不可提交的事务
                -- 表示在事务内部发生错误了。Xact_state()有三种值： - 1 .事务不可提交；
                -- 1 .事务可提交； 0 .表示没有事务，此时commit或者rollback会报错。
                if  xact_state() =- 1
                  rollback tran;
           end  catch
    end

## 二.使用`Set xact_abort off` ##

CREATE PROC Myprocedure 
    AS 
      BEGIN 
          SET xact_abort OFF; 
    
          BEGIN try 
              BEGIN TRAN  
              
              INSERT INTO teststu 
              VALUES     ( ' Terry ', 
                           ' boy ', 
                           23 ); 
    
              INSERT INTO teststu 
              VALUES     ( ' Mary ', 
                           ' girl ', 
                           21 ); 
    
              COMMIT TRAN 
          END try 
    
          BEGIN catch 
              -- 在此不可以使用xact_state来判断是否有不可提交的事务 
              -- 只可以使用@@Trancount来判断是否有还未提交的事务，未提交的事务未必 
              -- 就是不可提交的事务，所以使用@@TranCount > 0后就RollBack是不准确的 
              IF @@TranCount > 0 
                ROLLBACK TRAN; 
          END catch 
      END

另外，对于`@@Trancount`需要说明的是，`begin tran` 语句将 `@@Trancount`加 `1`。`Rollback tran`将 `@@Trancount`递减

## 存储过程中使用事务的简单语法 ##

在存储过程中使用事务时非常重要的，使用数据可以保持数据的关联完整性，在`Sql server`存储过程中使用事务也很简单，用一个例子来说明它的语法格式：

Create Procedure  MyProcedure
        (   @Param1       nvarchar(10),
           @param2    nvarchar(10)    )
        AS
           Begin
    Set NOCOUNT ON;
               Set XACT_ABORT ON;
               Begin  Tran
                  Delete from       table1 where name=’abc’;
                  Insert into       table2 values(value1,value2,value3);
               Commit Tran
           End

说明：  
1 、使用存储过程执行事物，需要开启`XACT_ABORT`参数(默认值为Off)，将该参数设置为On，表示当执行事务时，如果出错,会将`transcation`设置为`uncommittable`状态，那么在语句块批处理结束后将回滚所有操作；如果该参数设置为Off，表示当执行事务时，如果出错，出错的语句将不会执行，其他正确的操作继续执行。

2、当`SET NOCOUNT` 为 `ON` 时，不返回计数（计数表示受 `Transact-SQL` 语句影响的行数，例如在`Sql server`查询分析器中执行一个`delete`操作后，下方窗口会提示（3）Rows Affected）。当 `SET NOCOUNT` 为 `OFF` 时，返回计数,我们应该在存储过程的头部加上`SET NOCOUNT ON` 这样的话，在退出存储过程的时候加上 `SET NOCOUNT OFF`这样的话，以达到优化存储过程的目的。

## 二、事务内设置保存点 ##

用户可以在事务内设置保存点或标记。保存点定义如果有条件地取消事务的一部分，事务可以返回的位置。如果将事务回滚到保存点，则必须（如果需要，使用更多的 Transact-SQL 语句和 `COMMIT TRANSACTION` 语句）继续完成事务，或者必须（通过将事务回滚到其起始点）完全取消事务。若要取消整个事务，请使用 `ROLLBACK TRANSACTION transaction_name` 格式。这将撤消事务的所有语句和过程。如：

代码

CREATE PROCEDURE Myprocedure 
    AS 
      BEGIN 
          SET nocount ON; 
          SET xact_abort ON; 
    
          BEGIN TRAN ok 
    
          -- 开始一个事务OK 
          DELETE FROM rxqz 
          WHERE  qz = ' rx015 ' 
    
          -- 删除数据 
          SAVE TRAN bcd -- 保存一个事务点命名为bcd 
    
          UPDATE sz 
          SET    NAME = ' 李丽s ' 
          WHERE  NAME = ' 李丽 ' -- 修改数据 
    
          IF @@error <> 0 -- 判断修改数据有没有出错 
            BEGIN -- 如果出错 
                ROLLBACK TRAN bcd -- 回滚事务到BCD 的还原点 
                COMMIT TRAN ok -- 提交事务 
            END 
          ELSE -- 没有出错 
            COMMIT TRAN ok -- 提交事务 
      END

说明：1、`@@error`判断是否有错误，为`0`表示没有错误，但是对那种重大错误无法捕捉，而且`@@error`只能前一句`sql`语句生效。

## 三、存储过程使用`try…catch`捕获错误 ##

在存储过程中可以使用`try…catch`语句来捕获错误，如下：

CREATE PROCEDURE Myprocedure (@Param1 NVARCHAR( 10 ), 
                                  @param2 NVARCHAR( 10 )) 
    AS 
      BEGIN 
          SET nocount ON; 
    
          BEGIN try 
              DELETE FROM table1 
              WHERE  NAME = ’abc’; 
    
              INSERT INTO table2 
              VALUES     (value1, 
                          value2, 
                          value3); 
          END try 
    
          BEGIN catch 
              SELECT Error_number()  AS ErrorNumber, 
                     Error_message() AS ErrorMessage; 
          END catch 
      END

说明：

1、捕获错误的函数有很多，如下：

`ERROR_NUMBER()` 返回错误号。

`ERROR_SEVERITY()` 返回严重性。

`ERROR_STATE()` 返回错误状态号。

`ERROR_PROCEDURE()` 返回出现错误的存储过程或触发器的名称。

`ERROR_LINE()` 返回导致错误的例程中的行号。

`ERROR_MESSAGE()` 返回错误消息的完整文本。该文本可包括任何可替换参数所提供的值，如长度、对象名或时间。

2、有些错误，如`sql`语句中的表名称输入错误，这是数据库引擎无法解析这个表名称时，所发生的错误在当前的`try…catch`语句中无法捕获，必须由外层调用该存储过程的地方使用 `try…catch`来进行捕获。