Exception Handling in programming is very important. Of course, exception handling in Stored Procedures in SQL statements is also very important. explicit exception prompts can quickly find the root cause of the problem, saving a lot of time.
Next, we will take data insertion as an example to illustrate how to capture exceptions in SQL Server Stored Procedures.
- No char (10) primary key
- Name varchar (20)
- Comment varchar (50)
2. Stored Procedure
Take inserting data as an example. You can simply write other data.
Exceptions are captured and handled in programming languages, as is the case in SqlServer2008.
Add begin try ...... End try, and then catch exceptions: begin catch ...... End catch.
The detailed error code can be easily found.
The Code is as follows:
- Create proc sp_Insert_Course
- @ No char (10 ),
- @ Name varchar (20 ),
- @ Comment varchar (50 ),
- @ Rtn int output
- Begin try
- Insert into Course values (@ No, @ Name, @ Comment)
- Set @ rtn = 1
- End try
- Begin catch
- Set @ rtn = @ ERROR
- -- Auxiliary information
- -- Select ERROR_LINE () as Line,
- -- ERROR_MESSAGE () as message1,
- -- ERROR_NUMBER () as number,
- -- ERROR_PROCEDURE () as proc1,
- -- ERROR_SEVERITY () as severity,
- -- ERROR_STATE () as state1
- End catch
3. Stored Procedure execution
The related code is as follows:
- @ Rtn int
- Exec sp_Insert_Course '200', 'China', '', @ rtn output
- Print @ rtn
Normally, the returned value is 1,
If data with the ID "114" already exists, ERROR_CODE: 2627 is returned,
For other exceptions, the corresponding code is returned.
If the program has an exception, return the Exception Code and perform related processing.
Exception Handling in SQL Server is a little different from other databases (such as Oracle), but the basic idea is similar. Exceptions can be caught at the end.
Hope to help you.