在T-SQL中手动引发错误以跳转到BEGIN CATCH块 [英] Raise an error manually in T-SQL to jump to BEGIN CATCH block

问题描述

是否可以在存储过程中手动引发错误以停止执行并跳转到 BEGIN CATCH 块？ 的某些类似物在 C＃中抛出新的Exception（）。

Is it possible to raise an error in a stored procedure manually to stop execution and jump to BEGIN CATCH block? Some analog of throw new Exception() in C#.

这是我存储过程的主体：

Here is my stored procedure's body:

BEGIN TRY
BEGIN TRAN

-- do something

IF @foobar IS NULL
    -- here i want to raise an error to rollback transaction    

-- do something next

COMMIT TRAN
END TRY
BEGIN CATCH
    IF @@trancount > 0
        ROLLBACK TRAN
    END CATCH

我知道一种方法：选择1/0 但这太糟糕了！

I know one way: SELECT 1/0 But it's awful!!

推荐答案

您可以使用 raiserror 。在此处

-来自MSDN

BEGIN TRY
    -- RAISERROR with severity 11-19 will cause execution to 
    -- jump to the CATCH block.
    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();

    -- Use RAISERROR inside the CATCH block to return error
    -- information about the original error that caused
    -- execution to jump to the CATCH block.
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH;

编辑
如果您使用的是SQL Server 2012+可以使用 throw 子句。 此处是详细信息。

EDIT If you are using SQL Server 2012+ you can use throw clause. Here are the details.

这篇关于在T-SQL中手动引发错误以跳转到BEGIN CATCH块的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！