Tuesday, 11 June 2013

MSSQL - Function to format detailed error message

-- Drop Function if exists
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.fnGetDetailedErrorMessage'))
    DROP FUNCTION dbo.fnGetDetailedErrorMessage;

GO
/*=============================================
Author:
    Yuri Abele
Changes:
    11.06.2013 - Yuri Abele - initial
Description:
    Function to format last error as detailed message

Usage: Log last error
    BEGIN TRY
        DECLARE @i INT;
        PRINT 'Try division by zero'
        SET @i = 1/0;
        PRINT 'After'
    END TRY
    BEGIN CATCH
        -- Display error
        PRINT dbo.fnGetDetailedErrorMessage();
        IF @@TRANCOUNT > 0 ROLLBACK;
    END CATCH;
=============================================*/
CREATE FUNCTION dbo.fnGetDetailedErrorMessage()
RETURNS NVARCHAR(MAX)
AS
BEGIN
    RETURN ('ERROR:
        ErrorNumber        = ' + CAST(ERROR_NUMBER() AS VARCHAR(20)) + ',
        ErrorSeverity    = ' + CAST(ERROR_SEVERITY() AS VARCHAR(20)) + ',
        ErrorState        = ' + CAST(ERROR_STATE() AS VARCHAR(20)) + ',
        ErrorProcedure    = "' + ISNULL(ERROR_PROCEDURE(), '') + ',
        ErrorLine        = ' + CAST(ERROR_LINE() AS VARCHAR(20)) + ',
        ErrorMessage    = "' + ERROR_MESSAGE() + '"' + CHAR(13) + CHAR(10));
END;
GO

BEGIN TRY
    DECLARE @i INT;
    PRINT 'Try division by zero'
    SET @i = 1/0;
    PRINT 'After'
END TRY
BEGIN CATCH
    -- Display error
    PRINT dbo.fnGetDetailedErrorMessage();
    IF @@TRANCOUNT > 0 ROLLBACK;
END CATCH;

Results of execution: