Exception Handling in SQL Server Stored Procedures

Source: Internet
Author: User

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.

1. Environment
    • 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:

 
 
  1. Create proc sp_Insert_Course
  2. @ No char (10 ),
  3. @ Name varchar (20 ),
  4. @ Comment varchar (50 ),
  5. @ Rtn int output
  6. As
  7. Begin try
  8. Insert into Course values (@ No, @ Name, @ Comment)
  9. Set @ rtn = 1
  10. End try
  11. Begin catch
  12. Set @ rtn = @ ERROR
  13. -- Auxiliary information
  14. -- Select ERROR_LINE () as Line,
  15. -- ERROR_MESSAGE () as message1,
  16. -- ERROR_NUMBER () as number,
  17. -- ERROR_PROCEDURE () as proc1,
  18. -- ERROR_SEVERITY () as severity,
  19. -- ERROR_STATE () as state1
  20. End catch
3. Stored Procedure execution

The related code is as follows:

 
 
  1. Declare
  2. @ Rtn int
  3. Exec sp_Insert_Course '200', 'China', '', @ rtn output
  4. Print @ rtn

Execution result:

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.

4. Description

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.