Use things in Stored Procedures

I. Simple Syntax of transactions used in Stored Procedures

It is very important to use transactions in the stored procedure. Using data can maintain the integrity of the data association. It is also very easy to use transactions in the stored procedure of SQL Server, use an example to describe its syntax format:

Code Create procedure myprocedure

(@ Param1 nvarchar (10 ),

@ Param2 nvarchar (10)

)

As

Begin

Set nocount on;

Set xact_abort on;

Begin tran

Delete from Table1 where name = 'abc ';

Insert into Table2 values (value1, value2, value3 );

Commit tran

End

 

 

Note: 1. When using a stored procedure to execute a transaction, you must enable the xact_abort parameter (the default value is off) and set this parameter to on, indicating that if an error occurs during transaction execution, if transcation is set to the uncommittable status, all operations will be rolled back after the block batch processing of the statement is completed. If this parameter is set to off, it indicates that when a transaction is executed, if an error occurs, the wrong statement will not be executed, and other correct operations will continue to be executed.

2. When set nocount is on, no count is returned (counting indicates the number of rows affected by the transact-SQL statement. For example, after performing a delete operation in the SQL Server Query analyzer, the window below will prompt (3) rows affected ). When set nocount is off, the returned count is returned. If set nocount on is added to the header of the stored procedure and set nocount off is added to exit the stored procedure, to optimize the stored procedure.

 

Ii. Set the transaction retention point

You can set the save point or mark in the transaction. Save point defines the position that a transaction can return if a part of the transaction is canceled conditionally. If you roll back the transaction to the Save point, you must continue to complete the transaction (if necessary, use more Transact-SQL statements and commit transaction statements, or the transaction must be completely canceled (by rolling back the transaction to its starting point. To cancel the entire transaction, use the rollback transaction transaction_name format. This will cancel all the statements and processes of the transaction. For example:

Code Create procedure myprocedure

As

Begin

Set nocount on;

Set xact_abort on;


Begin Tran OK -- start a transaction OK

Delete from rxqz where qz = 'rx015' -- delete data

Save Tran BCD -- save a transaction point named BCD

Update SZ set name = 'Lili s' where name = 'Lili '-- modify data

If @ error <> 0 -- determines whether an error occurred while modifying the data.

Begin -- if an error occurs

Rollback Tran BCD -- roll back the transaction to the Restore Point of BCD

Commit Tran OK -- submit the transaction

End

Else -- no error

Commit Tran OK -- submit the transaction

End

 

 

Note: 1. @ error indicates whether an error exists. If it is 0, no error is returned. However, it cannot be captured for that major error, and @ error indicates that only the previous SQL statement can take effect.

 

Iii. Try... CatchCapture errors

Try... Catch statement to capture errors, as shown below:

Code Create procedure myprocedure

(@ Param1 nvarchar (10 ),

@ Param2 nvarchar (10)

)

As

Begin

Set nocount on;

Begin try

Delete from Table1 where name = 'abc ';

Insert into Table2 values (value1, value2, value3 );

End try

Begin catch

Select error_number () as errornumber,

Error_message () as errormessage;

End catch

End

 

 

Note: 1. There are many functions for capturing errors, as shown below:

Error_number () returns the error number.

Error_severity () returns the severity.

Error_state () returns the error Status Number.

Error_procedure () returns the name of the stored procedure or trigger with an error.

Error_line () returns the row number in the routine that causes the error.

Error_message () returns the complete text of the error message. This text can include any value provided by replaceable parameters, such as length, object name, or time.

2. Some errors, such as incorrect table name input in SQL statements, occur in the current try... The catch statement cannot be captured. The outer layer must use try… to call the stored procedure... Catch.

 

4. Transactions and try... CatchJoint Use

When transactions are used in the stored procedure, if there is no try... If an error occurs during set xact_abort on, the system automatically rolls back all SQL operations after the batch processing statement is completed. When set xact_abort off, if an error occurs, after the batch processing statement ends, the system will execute all statements without errors, and the statements with errors will not be executed.

If a transaction is used in a stored procedure, try... When an error is caught, you must manually perform the rollback operation in the Catch Block. Otherwise, the system will send an error message to the client. If you set xact_abort on at the beginning of the stored procedure, when an error occurs, the system sets the current transaction to uncommitted, that is, the xact_state () to-1, in this case, you can only perform rollback operations on transactions and not commit (COMMIT) operations. Then, in the catch statement block, you can perform the following operations based on xact_state () to determine whether a transaction is not committed. If yes, you can perform the rollback operation. If you set xact_abort off at the beginning of the stored procedure, when an error occurs, the system does not set xact_state () to-1, in the Catch Block, we cannot determine whether rollback is required based on the function value. However, we can determine whether rollback is required based on the @ trancount global variable, if the value of @ trancount is determined to be greater than 0 in the catch block, it indicates that there are still uncommitted transactions. Since the catch statement block is entered, there are still uncommitted transactions, the transaction must be rollback, but this method may be inaccurate in some cases. The recommended method is to set xact_abort on, and then judge the value of xact_state () in catch to determine whether rollback is required.

Here are two examples:

1. Use set xact_abort on

 

Code Create proc myprocedure

As

Begin

Set xact_abort on;

Begin try

Begin tran

Insert into teststu values ('terry ', 'Boy', 23 );

Insert into teststu values ('Mary ', 'girl', 21 );

Commit tran

End try

Begin catch

-- Xact_state () can be used to determine whether there are uncommitted transactions and uncommitted transactions.

-- Indicates an error occurred in the transaction. Xact_state () has three values:-1. The transaction cannot be committed;

-- 1. The transaction can be committed; 0. indicates that no transaction exists. At this time, the commit or rollback will report an error.

If xact_state () =-1

Rollback Tran;

End catch

End

 

 

 

II.Use set xact_abort off

 

Code Create proc myprocedure

As

Begin

Set xact_abort off;

Begin try

Begin tran

Insert into teststu values ('terry ', 'Boy', 23 );

Insert into teststu values ('Mary ', 'girl', 21 );

Commit tran

End try

Begin catch

-- Xact_state cannot be used to determine whether a transaction cannot be committed.

-- You can only use @ trancount to determine whether there are uncommitted transactions. uncommitted transactions are not necessarily

-- Is a non-commit transaction. Therefore, rollback is inaccurate when @ trancount> 0 is used.

If @ trancount> 0

Rollback Tran;

End catch

End

 

 

 

In addition, for @ trancount, it must be noted that the begin Tran statement adds @ trancount to 1. Rollback Tran degrades @ trancount to 0, except rollback Tran savepoint_name, which does not affect @ trancount. Commit Tran or commit work will decrease @ trancount by 1.

Use transactions in Stored Procedures)