Use transactions in SQL SERVER Stored Procedures

Create PROCEDURE UpdateWanjun
@ UserName nvarchar (500 ),
@ UserPassword nvarchar (500 ),
@ ReturnVal int output
AS
-- Set XAcT_ABORT ON
Begin Transaction T
Update admins set UserPassword = @ UserPassword where UserName = @ UserName
Update admins set UserPassword = @ UserPassword, a = 'aaadfasdfasdfas 'where id = 4 -- error statement. Type a is int and the default size is 4.
-- Set @ ReturnVal = @ Rowcount (this variable can only be used under the SQL statement next to it, so that the returned value can be returned. It can only be used under Update)

IF @ Error <> 0
Begin
Print '20140901'
RollBack Transaction T
End
Else
Print '20140901'
COMMIT Transaction T
GO

######################################## ######################################## ####

If you call a stored procedure on another server in a stored procedure, you need to use

Begin distributed transaction to start transactions (DISTRIBUTED transactions)

Begin distributed tran [SACTION]
[Transaction_name| @Tran_name_variable]

Parameters

Transaction_name

Is a user-defined transaction name used to track distributed transactions in the ms dtc utility.Transaction_nameMust comply with the identifier rules, but only use the first 32 characters.

@Tran_name_variable

Is a user-defined variable name that contains a transaction name used to track distributed transactions in the ms dtc utility. RequiredChar,Varchar,NcharOrNvarcharThis variable is declared as a data type.

Note

The server that executes the begin distributed transaction statement is the TRANSACTION creator and controls the completion of the TRANSACTION. When a connection sends a subsequent commit transaction or rollback transaction statement, the master server requests the ms dtc to manage the completion of distributed transactions between the involved servers.

There are two ways to register a remote SQL server in a distributed transaction:

• A registered connection in a distributed transaction executes a remote stored procedure call, which references a remote server.

• A registered connection in a distributed transaction executes a distributed query that references a remote server.

For exampleServerAInitiate begin distributed transaction, this connection is calledServerBStored Procedures andServerCAndServerCStored ProcedureServerDWhen a distributed query is executed, all the four SQL servers enter the distributed transaction.ServerAIs the creator and control server of the transaction.

The connection involved in the Distributed Transaction Transact-SQL does not obtain the transaction object that can be passed to another connection, and thus it cannot be explicitly registered in the distributed transaction. The only way for a remote server to register a transaction is to become the target of remote stored procedure calling or distributed query.

By default, any valid user has the transin distributed transaction permission.