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.