- catalog
- 0. Background
- 1. Create the exception information table ErrorLog
- 2. Establish a stored procedure to save exception information
- 3. Establish a stored procedure for printing exception information in SQL Server
- 4. Create a stored procedure for testing to throw exceptions for testing
- 5. Reference information
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