如何在MS SQL中提高存储过程中的错误

问题描述

大家好，

有人可以建议我如何在MS SQl Server中引发存储过程错误.

干杯

Hi All,

Can anybody suggest me how we can raise errors in stored procedure in MS SQl Server.

Cheers

推荐答案

DECLARE @DBID INT;
SET @DBID = DB_ID();

DECLARE @DBNAME NVARCHAR(128);
SET @DBNAME = DB_NAME();

RAISERROR
    (N'The current database ID is:%d, the database name is: %s.',
    10, -- Severity.
    1, -- State.
    @DBID, -- First substitution argument.
    @DBNAME); -- Second substitution argument.
GO

此示例使用用户定义的消息提供相同的信息.
其他

This example provides the same information using a user-defined message.
other

EXECUTE sp_dropmessage 50005;
GO
EXECUTE sp_addmessage 50005, -- Message id number.
    10, -- Severity.
    N'The current database ID is: %d, the database name is: %s.';
GO
DECLARE @DBID INT;
SET @DBID = DB_ID();

DECLARE @DBNAME NVARCHAR(128);
SET @DBNAME = DB_NAME();

RAISERROR (50005,
    10, -- Severity.
    1, -- State.
    @DBID, -- First substitution argument.
    @DBNAME); -- Second substitution argument.
GO

下面的代码示例演示如何在TRY块内使用RAISERROR导致执行跳转到关联的CATCH块.它还显示了如何使用RAISERROR返回有关调用CATCH块的错误的信息.
注意注意

RAISERROR只能生成状态为1到127的错误.由于数据库引擎可能会引发状态为0的错误，因此建议您先检查ERROR_STATE返回的错误状态，然后再将其作为值传递给RAISERROR的状态参数.
其他

The following code example shows how to use RAISERROR inside a TRY block to cause execution to jump to the associated CATCH block. It also shows how to use RAISERROR to return information about the error that invoked a CATCH block.
NoteNote

RAISERROR can generate errors with state from 1 through 127 only. Because the Database Engine may raise errors with state 0, we recommend that you check the error state returned by ERROR_STATE before passing it as a value to the state parameter of RAISERROR.
other

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;

这篇关于如何在MS SQL中提高存储过程中的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！