SQL Server exception Capture (Try Catch)

/*sqlserver exception capture in SQL Server database, if there are many stored procedures, we use dynamic SQL for stored procedure call stored procedure, at this time, it is possible to make a mistake at some point, but the error we can not trace to the wrong stored procedure, At this point we can use exception handling, log table to record the execution of the stored procedure, but navigate to the wrong stored procedure, the following is a small instance of a stored procedure exception handling: *//*=========================================== ========== related error message is as follows: Error_number () returns the error number. Error_severity () returns the severity. Error_state () returns the error status number. Error_procedure () returns the name of the stored procedure or trigger where the error occurred. Error_line () returns the line number in the routine that caused the error. Error_message () returns the full text of the error message. ========================================================*/create Table #LogTable/* You can build an entity table to hold the wrong information */(ID in  T identity,--error ordinal errornumber int,--error number errorseverity int,--severity errorstate int,--error status number errorproducure varchar,--The name of the stored procedure or trigger where the error occurred errorline int,--The line number in the routine that caused the error errormessage varchar (200)--The full text of the error message--========== ===== Divisor is not a 0 exception capture =================--if EXISTS (SELECT * from sysobjects WHERE id = object_id (N ' Testtrycatch ') and xtype = ' P ') DROP PROC testtrycatch; Gocreate TABLE #Department (DeptID varchaR (PRIMARY key,deptname NVARCHAR ()) Godelete from #DepartmentCREATE PROC testtrycatchas beginbegin try--start catching exception begin tran;--start transaction INSERT INTO #Department (DeptID, Deptname) VALUES (' D0001 ', N ' Human Resources team ')/* No transaction, each sentence is a transaction, data insertion #departmen T, and will not be rolled back later. */print ' before error ' SELECT 1/0;--business processing segment PRINT ' after error '/* No transaction is added, the statement after the error no longer executes */insert into #Department (deptid , deptname) VALUES (' D0002 ', N ' financial team ') Commit tran;--COMMIT transaction end try--Close catch exception begin catch--have exception arrested if @ @TRANCOUNT > 0--Judging there's nothing     Service Beginrollback tran;--rollback TRANSACTION end; DECLARE @ErrorMsg NVARCHAR (MAX); SELECT @ErrorMsg = Error_message ();        RAISERROR (@ErrorMsg, 16, 1); INSERT into #LogTable VALUES (Error_number (), error_severity (), Error_state (), Error_procedure (), Error_line (), Error_message ()); End catch;--exception handling end--execute stored procedure EXEC testtrycatch;--View Log table select *from #LogTable;--View System Log table Select *from sys.messageswhe RE message_id = 8134 and language_id = 2052;--View system messages table Select *from Sys.messageswhere  language_id = 2052ORDER by message_id; 

　　

SQL Server exception Capture (Try Catch)