1

I have stored procedure that process single row transaction. I would like to catch any errors and insert in Error table. Then use nightly process to forward the information from error table to developers. I'm wondering what is the best practice for this situation? Is there any good example on how to handle this? Here is my code and idea on what I have as of now:

CREATE PROCEDURE [dbo].[InsertBuilding] 
    @Status BIT = 0,
    @Number CHAR(4) = NULL,
    @Name VARCHAR(50) = NULL,
    @Contact VARCHAR(50) = NULL,
    @Address1 VARCHAR(50) = NULL,
    @Address2 VARCHAR(50) = NULL,
    @City VARCHAR(50) = NULL,
    @State CHAR(2) = NULL,
    @Zip CHAR(5) = NULL,
    @Phone CHAR(10) = NULL,
    @ActionID UNIQUEIDENTIFIER = NULL
AS
    SET NOCOUNT ON
    SET XACT_ABORT ON
    BEGIN
        BEGIN TRY
            INSERT INTO Buildings (
                Status, Number, Name,
                Contact, Address1, Address2,
                City, State, Zip, Phone,
                ActionDt, ActionID
            ) 
            OUTPUT INSERTED.RecID
            VALUES (
                @Status,@Number,@Name,
                @Contact,@Address1,@Address2,
                @City,@State,@Zip,@PhonePri,
                CURRENT_TIMESTAMP,@ActionID
            )
        END TRY
        BEGIN CATCH
            INSERT INTO dbo.ERROR(
                ErrorNumber, 
                ErrorSeverity, 
                ErrorState, 
                ErrorProcedure, 
                ErrorLine, 
                ErrorMessage, 
                ErrorDateTime
            )VALUES(
                ERROR_NUMBER(), 
                ERROR_SEVERITY(), 
                ERROR_STATE(), 
                ERROR_PROCEDURE(), 
                ERROR_LINE(), 
                ERROR_MESSAGE(), 
                CURRENT_TIMESTAMP()
            )
        END CATCH
    END;

If anyone have any suggestions or example how this can be improved or good way to deliver these errors to developers please let know. Thank you.

1

The below is what i use to catch errors and send it of to the developers. It generates a mail every time an error occurs

BEGIN CATCH  

(SELECT @Servername AS ServerName, 

 ERROR_NUMBER() AS ErrorNumber  

,ERROR_SEVERITY() AS ErrorSeverity  

,ERROR_STATE() AS ErrorState  

,ERROR_PROCEDURE() AS ErrorProcedure  

,ERROR_MESSAGE() AS ErrorMessage

INTO #ERROR )



/*************************************************************/  

/****************** HTML Preparation *************************/  

/*************************************************************/  



DECLARE @HTML  VARCHAR(MAX),

    @table VARCHAR(MAX)



 SET @HTML = --HTML layout--

            '<html><head>' +

            '<H1 style="color: #000000">Error Executing GetAllJobStatuses</H1>' +

            '<style>' +

            'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:9pt;color:Black;} ' +

            '</style>' +

            '</head>' +

            '<body><table cellpadding=0 cellspacing=0 border=0>' +

            '<tr bgcolor=33ffff>'+  

            '<td align=center>Server Name</b></td>' + 

            '<td align=center>Error Number</b></td>' + 

            '<td align=center>Error Severity</b></td>' + 

            '<td align=center>Error State</b></td>' + 

            '<td alignISNULL(=center>Error Procedure</b></td>' +  

            '<td align=center>Error Message</b></td></tr>';                                



 SELECT @table = CONVERT(nvarchar(max) ,

(SELECT

 td = ServerName, '', 

 td = CAST(ErrorNumber as varchar(30)), '',  

 td = CAST(ErrorSeverity as varchar(30)), '', 

 td = CAST(ErrorState as varchar(30)), '', 

 td = ISNULL(CONVERT(VARCHAR(50),[ErrorProcedure] ),''), '',  

 td = ErrorMessage, ''                                    

 FROM                                   

#ERROR

FOR XML PATH(N'tr'), TYPE));



SET @HTML = @HTML + @table + CHAR(10) + 

         N'</table></body></html>';

SET @HTML = @HTML + '<TR></TR><B>End of Report</B></TABLE></BODY></HTML>';

--PRINT @HTML



Declare @MailSubject varchar(100)

SET @MailSubject = 'Error Executing GetAllJobStatuses!! - ' + 
DATENAME(weekday, getdate())

IF LEN(@HTML) > 10      



EXEC msdb.dbo.sp_send_dbmail

@recipients = 'tester@test.com',

@body_format =  'HTML',

@body= @HTML,

@subject = @MailSubject,

@profile_name = 'SQLDBA'

    --SELECT * FROM #ERROR



 DROP TABLE #ERROR 

 END CATCH; 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.