How to catch exceptions by stored procedures in SqlServer

In this issue, the editor will bring you about how stored procedures in SqlServer catch exceptions. The article is rich in content and analyzes and describes it from a professional point of view. I hope you can get something after reading this article.

How to catch exceptions by stored procedures in SqlServer

1. Environment

The database is SqlServer2008. The structure of the Course is: Nochar (10) primarykeyNamevarchar (20) Commentvarchar (50)

2. Stored procedure

Take inserting data as an example, the rest can be written according to it.

All programming languages have exception catching and handling, and the same is true in SqlServer2008.

Add begintry to the statement that will cause an exception. Endtry, and then catch the exception: begincatch... Endcatch is fine.

A detailed explanation of the error code is easy to find.

The code is as follows:

Createprocsp_Insert_Course@Nochar (10), @ Namevarchar (20), @ Commentvarchar (50), @ rtnintoutputasbegintryinsertintoCoursevalues (@ No,@Name,@Comment) set@rtn=1endtrybegincatchset@rtn=@@ERROR-- Auxiliary Information-selectERROR_LINE () asLine,--ERROR_MESSAGE () asmessage1,--ERROR_NUMBER () asnumber,--ERROR_PROCEDURE () asproc1,--ERROR_SEVERITY () asseverity,--ERROR_STATE () asstate1endcatch

How to catch exceptions by stored procedures in SqlServer

3. Stored procedure execution

The related code is as follows:

Declare@rtnintexecsp_Insert_Course'114',' language','', @ rtnoutputprint@rtn

Execution result:

Normally, the return value is 1

If the data number "114" already exists, ERROR_CODE:2627 will be returned.

Other exceptions will return the corresponding code.

4. Description

If there is an exception in the program, return the exception code, and then deal with it.

Exception handling in SQLServer is a little different from that in other databases (such as Oracle), but the basic idea is similar, and exceptions can be obtained at the end of the catch.

This is how the stored procedure in SqlServer shared by Xiaobian catches exceptions. If you happen to have similar doubts, please refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.