sql-server - 从存储过程返回错误消息

Question

问题应该很简单，但我不知道答案，也不知道为什么我的存储过程不起作用。

CREATE PROCEDURE spTest_Delete
@ID int
AS
    begin tran
        declare @err int
        declare @errMesage nvarchar(max)
        set @errMesage = ''
        set @err = 0

        delete from Test
        where ID = @ID

        set @err = @@ERROR
        set @errMesage = ERROR_MESSAGE()

        if @err = 0 
           commit tran
        else 
        begin
            RAISERROR(N'Could not delete !Error nr: %d. Message: %s', 1, 16, @err, @errMesage)
            rollback tran
        end

此过程运行正常，但在 delete 语句上存在 FK 约束的情况下，它会遇到错误(这很好)，我想捕获该错误。

Msg 547, Level 16, State 0, Procedure spTest_Delete, Line 12
The DELETE statement conflicted with the REFERENCE constraint "FK_TEstFK_Test". The conflict occurred in database "Test", table "dbo.Test", column 'ID'. The statement has been terminated.

Could not delete!
Error nr: 547. Message: (null) Msg 50000, Level 1, State 16

即使 delete 语句抛出错误，我的消息变量始终为 null。

Answer 1

您可能想开始使用TRY..CATCH阻止您的程序

Implements error handling for Transact-SQL that is similar to the exception handling in the Microsoft Visual C# and Microsoft Visual C++ languages. A group of Transact-SQL statements can be enclosed in a TRY block. If an error occurs in the TRY block, control is passed to another group of statements that is enclosed in a CATCH block.

所以你的程序可以重写为:

CREATE PROCEDURE spTest_Delete @ID INT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION
            DELETE
            FROM Test
            WHERE ID = @ID;
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION
        SELECT ERROR_NUMBER(), ERROR_MESSAGE();
    END CATCH
END

另外，请注意您正在作为单个删除语句运行。这意味着它不需要包含在事务中。 This问题解释了原因。

你的代码变成这样:

CREATE PROCEDURE spTest_Delete @ID INT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        DELETE
        FROM Test
        WHERE ID = @ID;
    END TRY
    BEGIN CATCH
        SELECT ERROR_NUMBER(), ERROR_MESSAGE();
    END CATCH
END

现在为什么你的@errMessage总是NULL？因为 ERROR_MESSAGE() 仅在 CATCH BLOCK 中有效。写在 documentation :

Returns the message text of the error that caused the CATCH block of a TRY…CATCH construct to be run.

Using TRY..CATCH in Transact-SQL告诉我们:

Error information is retrieved by using these functions from anywhere in the scope of the CATCH block of a TRY…CATCH construct. The error functions will return NULL if called outside the scope of a CATCH block.