Stored procedure - exception capture & printing exception information

Shanzm - May 13, 2020

0. Background

I used to complain about using stored procedures. I felt that stored procedures should not appear in new projects,

But recently, when we study stored procedures, we find that the advantages of stored procedures are dazzling!

It's a bit confusing to focus on the shortcomings of stored procedures.

The previous week, I groped for the stored procedure I wrote, which was too childish and irregular.

Before Stored procedure -- a simple example of calling stored procedures in C# Middle,

We define a temporary variable @ sum in the transaction of the stored procedure. After every sql statement of the transaction, @ sum+@@error. Finally, we judge whether there is an exception according to whether @ sum is 0,

If there is no exception, @ @ error is 0, if there is an exception, @ @ error value is the error code, that is, it must not be 0

Therefore, you can judge whether there is an exception through the final @ sum,

But it's important to note that @ error can't catch that kind of major error, and it only takes effect on the previous sql statement

Therefore, it is recommended to use TRY CATCH

Here, we define a stored procedure to catch exceptions and record the exceptions in the exception information table of the database.

In this example, all stored procedures are performed in a new test database ShanTest database


1. Create the exception information table ErrorLog

USE [ShanTest]
GO
/****** Object:  Table [dbo].[ErrorLog]    Script Date: 2020-05-11 14:49:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ErrorLog](
	[ErrorLogID] [int] IDENTITY(1,1) NOT NULL,--Anomaly table ID
	[ErrorTime] [datetime] NOT NULL CONSTRAINT [DF_ErrorLog_ErrorTime]  DEFAULT (getdate()),--Exception time. The default value is the current time
	[UserName] [sysname] NOT NULL,          --Abnormal user name, here is dbo,dbo Is the default user of each database, with owner permission, full name: datebaseOwner
	[ErrorNumber] [int] NOT NULL,           --Exception code
	[ErrorSeverity] [int] NULL,             --Abnormal severity
	[ErrorState] [int] NULL,                --Abnormal status code
	[ErrorProcedure] [nvarchar](126) NULL,  --Throw exception stored procedure
	[ErrorLine] [int] NULL,                 --Number of error lines
	[ErrorMessage] [nvarchar](4000) NOT NULL,--Complete exception information
 CONSTRAINT [PK_ErrorLog_ErrorLogID] PRIMARY KEY CLUSTERED 
(
	[ErrorLogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

The fields in the exception information table are as follows:


2. Establish a stored procedure to save exception information

USE [ShanTest]
GO
/****** Object:  StoredProcedure [dbo].[pro_ErrorLog]    Script Date: 2020-05-11 14:15:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =================================================
-- Author:		shanzm
-- Create date: 2020 May 11, 2010 
-- Description:	Save exceptions caught in stored procedures to ErrorLog surface
-- =================================================
CREATE PROCEDURE [dbo].[pro_ErrorLog]
@ErrorLogID [int] = 0 OUTPUT 
AS                               
BEGIN
    SET NOCOUNT ON;

        INSERT [dbo].[ErrorLog]
            (
            [UserName],
            [ErrorNumber],
            [ErrorSeverity],
            [ErrorState],
            [ErrorProcedure],
            [ErrorLine],
            [ErrorMessage]
            )
        VALUES
            (
            CONVERT(sysname, CURRENT_USER),--current_user ,The value here is dbo,dbo Is the default user for each database with owner rights
										   --sysname Type is used for table columns, variables and stored procedure parameters for storing object names, equivalent to nvachart(120)
            ERROR_NUMBER(),                 --Error code, there are many error codes, you can baidu
            ERROR_SEVERITY(),               --Severity of error
            ERROR_STATE(),                  --Bad status code
            ERROR_PROCEDURE(),              --Bad stored procedure
            ERROR_LINE(),                   --Wrong line number
            ERROR_MESSAGE()                 --error message
            );
        SET @ErrorLogID = @@IDENTITY;--@@IDENTITY Automatically generated when inserting records ID
		execute dbo.pro_PrintError;--Changing the stored procedure will ERROR_MESSAGE()stay sql server Print out the information window
END;

3. Establish a stored procedure for printing exception information in SQL Server

After the exception information is stored in the stored procedure pro? Errorlog, the stored procedure is called

USE [ShanTest]
GO
/****** Object:  StoredProcedure [dbo].[pro_PrintError]    Script Date: 2020-05-11 14:43:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		shanzm
-- Create date: 2020 May 11, 2010 
-- Description:	Print exception information in message box
-- =============================================
CREATE PROCEDURE [dbo].[pro_PrintError]
AS
BEGIN
    SET NOCOUNT ON;

    -- Print error information.
    PRINT 'ErrorNumber :  ' +CONVERT(varchar(50), ERROR_NUMBER()) 
    PRINT 'ErrorSeverity :  ' + CONVERT(varchar(5), ERROR_SEVERITY()) 
    PRINT 'ErrorState : ' + CONVERT(varchar(5), ERROR_STATE()) 
    PRINT 'ErrorProcedure : ' + ISNULL(ERROR_PROCEDURE(), '-') 
    PRINT 'ErrorLine : ' + CONVERT(varchar(5), ERROR_LINE());
    PRINT 'ErrorMessage : ' + ERROR_MESSAGE();
END;

4. Create a stored procedure for testing to throw exceptions for testing

Remember that when we need to use a stored procedure in our business, once a transaction is used, we must judge whether there is an exception thrown in the BEGIN CATCH statement. If there is an exception thrown, the transaction in the stored procedure must be ROLLBACK

USE [ShanTest]
GO
/****** Object:  StoredProcedure [dbo].[TestErrorLog]    Script Date: 2020-05-11 15:14:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		shanzm
-- Create date: 2020 May 11, 2010
-- Description:	Used to throw exception tests ErrorLog Available or not
-- =============================================
ALTER PROCEDURE [dbo].[TestErrorLog]   
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY---------Start catching exceptions
        BEGIN TRANSACTION------------------Start transaction
		update  ShanTest.dbo.Product set Name=NULL where Id=1--Here, you can create a database, a table, and insert a column that is not allowed to be empty in the table NUll
		--select 1/0;
        COMMIT TRAN -----------------------Commit transaction
    END TRY-----------End catch exception

    BEGIN CATCH------Exception caught
        IF @@TRANCOUNT > 0---------------------Judge whether there is any business
        BEGIN
            ROLLBACK TRAN----------------------Rollback transaction
        END
        EXEC pro_ErrorLog----------------------Execute the stored procedure to record the error information in the table
    END CATCH--------End exception handling
END

Execute the stored procedure to test:

USE [ShanTest]
GO
EXEC [dbo].[TestErrorLog]
GO

Test results:
Real message in sql service message box:

ErrorNumber :  515
ErrorSeverity :  16
ErrorState : 2
ErrorProcedure : TestErrorLog
ErrorLine : 13
ErrorMessage : Value cannot be NULL Insert column 'Name',surface 'ShanTest.dbo.Product';Column not allowed Null Value. UPDATE Failed.

The message is printed by the pro_PrintError stored procedure

At the same time, a record is added to the ErrorLog table:


5. Reference information

Blog Park: SQL Server exception capture
Blog Park: SQL server stored procedure try catch TRANSACTION

Tags: Stored Procedure SQL Database

Posted on Wed, 13 May 2020 21:17:48 -0400 by solodesignz