SQL Server :获取引发错误的查询

Question

当发出错误信号时，我使用usp_RethrowError (在technet.microsoft站点上的Using TRY...CATCH in Transact-SQL文章中作为例子给出)。

有没有办法在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仍然是您的朋友。这不是世界上最有效的东西，但它会找出用户输入的是什么(而不是过程中的语句)，但这可能会揭示错误发生时使用的参数是什么？

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