Create a stored procedure in SQL Server that contains transactions

what is a transactionA logical unit that contains 1 or more statements when a transaction occurs. A statement in a transaction is a whole, either committed together or revoked together. A transaction can be rolled back before it is committed, and it cannot be undone once it is committed, and is permanently modified. Why use TransactionsExamples of life can be exemplified, such as bank transfers: A to B to 1 million. Sequence of execution of the program: 1. A account minus 1,000,002. b account increased by 1 million. If all is done successfully, assuming that 1 executes successfully and 2 execution fails, there will be a problem. This problem does not occur with transactions, because if one of the operations fails, the transaction is rolled back and all previous operations are undone. Basic control statements for transactions1.BEGIN TRANSACTION: Transaction start 2.COMMIT TRANSACTION: Transaction commit 3. ROLLBACK TRANSACTION: Transaction rollback Characteristics of the transaction (ACID)1.ATOMIC (atomicity): In a transaction, a program is a logical unit of work for a database, and modifications to the data are either fully executed or not executed at all. 2.CONSISTENT (consistency): data is consistent before and after transaction execution, and changes to the data are not visible until the transaction is complete. 3.ISOLATED (Isolation): Concurrent transactions cannot interfere with each other 4.DURABLE (persistence): Once a transaction is committed, it is a permanent modification of the data. The following is an example of a stored procedure that contains transactions:

1 ALTER proc [dbo].[proc_insertstudent]2 @stuName nvarchar( -),@stuClassId int,@stuAge int3  as4 begin5 SetNocount on  --On means no count is returned6 SetXact_abort on  --When a transaction is executed, the transcation is set to the uncommittable state if an error occurs7 8 beginTry9 Declare @stuCountByName int;Ten Select @stuCountByName=Count(*) fromStudentswhereName=@stuName; One  A if(IsNull(@stuName,"')="') - begin - Print('the name cannot be empty'); the return; - End -  - if(@stuCountByName>0) + begin - Print('Duplicate name'); + return A End at  - begin Tran  --Open Transaction - Insert  intoStudents (Name,classid,age)Values(@stuName,@stuClassId,@stuAge) - Commit Tran  --Commit a transaction -  - EndTry in  - beginCatch to ifXact_state ()=-1 + rollback Tran;--rolling back a transaction - SelectError_number () aserrornumber; the SelectError_message () aserrormsg; * EndCatch $ SetXact_abortoff;Panax Notoginseng End

View Code

Where students table:

1 CREATE TABLE [dbo].[Students](2  [ID] [int] IDENTITY(1,1) not NULL Primary Key,3  [Name] [nvarchar]( -) not NULL,4  [ClassId] [int]  not NULL,5  [ Age] [int]  not NULL,6  [Createtime] [datetime]  not NULL7);

View Code

Create a stored procedure in SQL Server that contains transactions