从存储过程返回错误信息 [英] Return error message from stored procedure

问题描述

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

The question should be quite simple, but I can't figure out the answer nor why my stored procedure is not working.

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 约束的情况下，它会遇到错误(这很好)，我想捕获错误.

This procedure runs ok, but in case of FK constraint on the delete statement it runs into an error (which is good) and I would like to catch the error.

消息 547，级别 16，状态 0，过程 spTest_Delete，第 12 行
DELETE 语句与 REFERENCE 约束冲突FK_TEstFK_Test".数据库Test"、表中发生冲突dbo.Test"，ID"列.该语句已终止.

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.

无法删除！
错误编号:547.消息:(空)消息 50000，级别 1，状态 16

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

我的消息变量总是为空，即使 delete 语句抛出错误.

I always get null for my message variable, even though the delete statement throws an error.

推荐答案

尝试使用 TRY CATCH 并捕获您的错误，如下所示:

Try to use TRY CATCH and catch your error like this:

BEGIN TRY
   delete from Test
    where  ID = @ID
END TRY
BEGIN CATCH
    SET @ErrorMessage  = ERROR_MESSAGE()
    SET @ErrorSeverity = ERROR_SEVERITY()
    SET @ErrorState    = ERROR_STATE()
    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)
    BREAK
END CATCH

这篇关于从存储过程返回错误信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！