sql-server - SQL Server:获取查询引发错误

Question

当信号出问题时，我正在使用usp_RethrowError(作为示例在Using TRY...CATCH in Transact-SQL网站上的technet.microsoft文章中给出)。

有什么方法可以在usp_RethrowError过程中获取触发此错误的查询？我还想将查询文本添加到@ErrorMessage。

您可以在下面找到usp_RethrowError存储过程的代码:

CREATE PROCEDURE usp_RethrowError AS
    -- Return if there is no error information to retrieve.
    IF ERROR_NUMBER() IS NULL
        RETURN;

    DECLARE 
        @ErrorMessage    NVARCHAR(4000),
        @ErrorNumber     INT,
        @ErrorSeverity   INT,
        @ErrorState      INT,
        @ErrorLine       INT,
        @ErrorProcedure  NVARCHAR(200);

    -- Assign variables to error-handling functions that 
    -- capture information for RAISERROR.
    SELECT 
        @ErrorNumber = ERROR_NUMBER(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE(),
        @ErrorLine = ERROR_LINE(),
        @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

    -- Build the message string that will contain original
    -- error information.
    SELECT @ErrorMessage = 
        N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 
            'Message: '+ ERROR_MESSAGE();

    -- Raise an error: msg_str parameter of RAISERROR will contain
    -- the original error information.
    RAISERROR 
        (
        @ErrorMessage, 
        @ErrorSeverity, 
        1,               
        @ErrorNumber,    -- parameter: original error number.
        @ErrorSeverity,  -- parameter: original error severity.
        @ErrorState,     -- parameter: original error state.
        @ErrorProcedure, -- parameter: original error procedure name.
        @ErrorLine       -- parameter: original error line number.
        );
GO

Answer 1

不幸的是，DMV仍然很难获得此信息，因为它们存储过程的sql_text而不是用户实际执行的操作。但是，在这种情况下，DBCC仍然是您的 friend 。这不是世界上最高效的事情，但它可以弄清楚用户输入的内容(不是过程中的语句)，但是这可以阐明发生错误时正在使用哪些参数？

ALTER PROCEDURE dbo.usp_RethrowError 
AS
BEGIN
    SET NOCOUNT ON;

    -- Return if there is no error information to retrieve.
    IF ERROR_NUMBER() IS NULL
        RETURN;

    DECLARE 
        @ErrorMessage    NVARCHAR(MAX),
        @ErrorNumber     INT,
        @ErrorSeverity   INT,
        @ErrorState      INT,
        @ErrorLine       INT,
        @ErrorProcedure  NVARCHAR(200);

    -- Assign variables to error-handling functions that 
    -- capture information for RAISERROR.
    SELECT 
        @ErrorNumber = ERROR_NUMBER(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE(),
        @ErrorLine = ERROR_LINE(),
        @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

    -- Build the message string that will contain original
    -- error information.
    SELECT @ErrorMessage = 
        N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 
            'Message: '+ ERROR_MESSAGE();

    DECLARE 
        @sql NVARCHAR(255),
        @original_statement NVARCHAR(MAX);

    SET @sql = N'DBCC INPUTBUFFER(' + RTRIM(@@SPID) + ');';

    CREATE TABLE #dbcc
    (
        EventType SYSNAME,
        Parameters INT,
        EventInfo NVARCHAR(MAX)
    );

    INSERT #DBCC EXEC(@sql);

    SELECT TOP 1 @original_statement = EventInfo
        FROM #dbcc;

    SET @ErrorMessage = @ErrorMessage + N'
        Original statement:
        ' + @original_statement + '
        ';

    -- Raise an error: msg_str parameter of RAISERROR will contain
    -- the original error information.
    RAISERROR 
        (
        @ErrorMessage, 
        @ErrorSeverity, 
        1,               
        @ErrorNumber,    -- parameter: original error number.
        @ErrorSeverity,  -- parameter: original error severity.
        @ErrorState,     -- parameter: original error state.
        @ErrorProcedure, -- parameter: original error procedure name.
        @ErrorLine       -- parameter: original error line number.
        );
END
GO