Stored Procedure: Exception Handling

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

(1) The database is SQL server2008.

(2) The structure of the table (course) is:

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.

Programming LanguageException capture and processing are available, as is the case in sqlserver2008.

Add begin try ...... End try, and then catch exceptions: Begin catch ...... End catch.

ErrorCodeDetails 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   
  14     --  Auxiliary Information  
  15      --  Select error_line () as line,  
  16       --  Error_message () as message1,  
  17       --  Error_number () as number, 
  18       --  Error_procedure () as proc1,  
  19       --  Error_severity () as severity,  
  20       -- Error_state () as state1  
  21       End  Catch 

3. Stored Procedure execution

The related code is as follows:

 1   Declare  
  2     @ RTN     Int  
 3     Exec  Sp_insert_course  '  114  '  ,  '  Chinese  '  ,  ''  ,  @ 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

IfProgramIf an exception exists, 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.