Transact SQL – Exception Handling

Background

From v2005, Transact SQL has pretty good exception handling.

Based on begin Begin Try/End and Begin Catch /End Catch.

Code

To capture the actual error information, one can use a function like the one pasted below:

[dbo].fn_GetErrorInfo


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/*
    DROP FUNCTION [dbo].usp_GetErrorInfo
*/
if object_id('[dbo].[fn_GetErrorInfo]') is not null
begin
    set noexec on
end
go

CREATE FUNCTION [dbo].fn_GetErrorInfo()
RETURNS varchar(8000)
AS
BEGIN

    return 1/0;

END
go

set noexec off
go

ALTER FUNCTION [dbo].[fn_GetErrorInfo]()
RETURNS varchar(8000)
with schemabinding
AS
BEGIN

    -- ============================================================================================
    -- Author:		Daniel Adeniji
    -- Create date: 2019-02-04
    -- Description:	Error Handling - Get Error Information
    -- ==============================================================================================
    return
    (
              'ERROR_PROCEDURE :- '
            + ERROR_PROCEDURE()
            + CHAR(13)+ CHAR(10)

            + 'ERROR_MESSAGE :- '
            + ERROR_MESSAGE()
            + CHAR(13)+ CHAR(10)

            + 'ERROR_LINE :- '
            + convert(varchar(30), ERROR_LINE())
            + CHAR(13)+ CHAR(10)

            + 'ERROR_NUMBER :- '
            + convert(varchar(30), ERROR_NUMBER())
            + CHAR(13)+ CHAR(10)

            + 'ERROR_SEVERITY() :- '
            + convert(varchar(30), ERROR_SEVERITY())
            + CHAR(13)+ CHAR(10)

    )

END
GO

grant execute on [dbo].[fn_GetErrorInfo] to [public]
go

Output

Sample Output

rds_backup_database


ERROR_PROCEDURE :- rds_backup_database
ERROR_MESSAGE :- Database backups can only be performed by members of db_owner or db_backupoperator roles in the source database
ERROR_LINE :- 93
ERROR_NUMBER :- 50000
ERROR_SEVERITY() :- 16

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s