SQL Exception Handling begin try end try begin catch end catch

Summarized the following error capture methods: Try catch, @ error, raiserror

This is an exception handling during database conversion,

Begin try
InsertIntoSDT. DBO. dyemplosttm (logdate, prodgroup, shiftcode, employeeno, Mono,
Opno, otflag, losttypeid, offstdtime, employeegroup, cmflag)
Values (@ logdate, @ prodgroup, @ shiftcode, @ employeeno, @ mono, @ opno, @ otflag,
@ Losttypeid, @ offstdtime, @ employeegroup, @ cmflag );

End try

Begin catch
Declare@ Error int
Set@ Error =Error_number ()
If@ Error = 2627.
InsertIntoMigration. DBO. dyemplosttmerror (logdate, prodgroup, shiftcode, employeeno, Mono,
Opno, otflag, losttypeid, offstdtime, employeegroup, cmflag, errordesc)
Values (@ logdate, @ prodgroup, @ shiftcode, @ employeeno, @ mono, @ opno, @ otflag,
@ Losttypeid, @ offstdtime, @ employeegroup, @ cmflag,Error_message ());
End catch

 

ErrorCode2627: indicates
% Ls constraint '%. * ls' is violated '. Duplicate keys cannot be inserted in the object '%. * ls.

Each database engine error contains the following attributes:

Error code, message string, severity, status, process name, row number.

Error_number (): Error Code returned

Error_message (): Error message returned

Error_line (): The row where the error is returned.

Error_procedure (): Returns the name of the stored procedure or trigger with an error. If no error occurs in the stored procedure or trigger, the function returns NULL.

Error_state (): Return status

Error_severity ():Return severity

Bytes ------------------------------------------------------------------------------------------

 

 @ Rowcount Returns the number of rows affected by the previous statement.Over 2 billion rows UseRowcount_big

 

After a SELECT statement, this function returns the number of rows returned by the SELECT statement.

After an insert, update, or delete statement, this function returns the number of rows affected by the data modification statement.

If, after a statement that does not return rows, the function returns 0.

---------------------------------------------------------------------------

If both @ rowcount and @ error are used after a statement, you must put the two into one statement.

Update film set filname = 'A'
Go
Print @ Error

Go
Print @ rowcount
The result returned by running this statement is:

MSG 207, level 16, state 1, line 1
Invalid column name 'filname '.
207
0
While running

Update film set filname = 'A'
Go
Print @ rowcount

Go
Print @ Error

The returned result is:

MSG 207, level 16, state 1, line 1
Invalid column name 'filname '.
0
0

From the two instances, we can see that if you want to check whether the statement is successful after running,Followed by @ Error. Other statements are not allowed:

 

Update film set filname = 'A'
Go
Declare @ error int;
Declare @ rowcount int;

Select @ error = @ error, @ rowcount = @ rowcount

Print @ rowcount
Print @ Error

--------------------------------------------------------------------------------

If the try... Catch Block is not processed, the database engine terminates the connection with a severity of 20 or higher. However, as long as the connection is not terminated, try... catch will handle errors with a severity of 20 or higher.

 

An error with a severity of 10 or lower is considered as a warning or informative message. Try... Catch Block does not handle this type of error.

 

---------------------------------------------------------- Raiserror exception

Begin catch
Set @ error = error_number ()
If @ error <> 2627
Raiserror ('unexpected error: "% I" ', 16,1, @ error) with seterror
End catch