SQL Server : Getting the query raising an error

Question

I'm using the usp_RethrowError ( given as example in Using TRY...CATCH in Transact-SQL article on technet.microsoft site ) when signaling something is wrong.

Is there any way to get the query which triggers this error inside the usp_RethrowError procedure ? I would also like to add the query text to the @ErrorMessage.

You can find below the code for usp_RethrowError stored procedure :

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

Unfortunately the DMVs still make it difficult to get at this information because they store the sql_text for the procedure rather than what the user actually did. However DBCC is still your friend in a scenario like this. Not the most efficient thing in the world but it will figure out what the user entered (not the statement in the procedure) but this could shed light on what parameters are being used when the error happens?

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