SQL Server 2000 nested Stored Procedure transaction rollback

/* When multiple stored procedures are called in a large SQL statement and multiple stored procedures are called in the called stored procedure, after multiple nested calls, if you need to roll back the entire transaction if the primary key is repeated or the business logic is not met, you can use the following mode for processing. You can also directly count data without raiserror. If an error occurs after the set xact_abort on operation, the transaction is automatically rolled back to the outermost layer. However, if a business logic error occurs, manual counting and manual rollback are required, that is, the following declare @ errorsave int set @ errorsave = 0 | execute @ errorsave = ttransproc @ prikey, @ charcol/* call the Stored Procedure */| set @ errorsave = @ errorsave + @ error | return @ errorsave */

/* Create a test table */
Create Table testtrans (COLA int not null, COLB char (3) not null, constraint pk_testtrans primary key (COLA ))
Go
/* Create a stored procedure */
Set quoted_identifier on
Go
Set ansi_nulls on
Go
/* Stored procedure 3 */
Create procedure ttransproc @ prikey int, @ charcol char (3)
As
Declare @ errorsave int set @ errorsave = 0
Insert into testtrans values (@ prikey + 2, @ charcol)
If (@ prikey = 0) begin
Raiserror ('ttransproc user error', 16, 1)
Set @ errorsave = @ errorsave + @ Error
End
Return @ errorsave
Go
/* Stored Procedure 2 */
Create procedure stransproc @ prikey int, @ charcol char (3)
As
Declare @ errorsave int set @ errorsave = 0
Insert into testtrans values (@ prikey + 1, @ charcol)
Execute @ errorsave = ttransproc @ prikey, @ charcol/* call the stored procedure 3 */
If (@ prikey = 6) begin
Raiserror ('stransproc user error', 16, 1)
Set @ errorsave = @ errorsave + @ Error
End
Return @ errorsave
Go
/* Stored Procedure 1 */
Create procedure ftransproc @ prikey int, @ charcol char (3)
As
Declare @ errorsave int set @ errorsave = 0
Insert into testtrans values (@ prikey, @ charcol)
Execute @ errorsave = stransproc @ prikey, @ charcol/* Call Stored Procedure 2 */
If (@ prikey = 11) begin
Raiserror ('ftransproc user error', 16, 1)
Set @ errorsave = @ errorsave + @ Error
End
Return @ errorsave
Go

Set quoted_identifier off
Go
Set ansi_nulls on
Go
--------------------- Test ----------------------
/* Set this flag to automatically roll back when an error occurs during execution */
Set xact_abort on
/* This variable is used to save user-defined errors. For example, when the business requirements are not met, raiserror is used to throw an error and count the error so that the outer transaction can be rolled back */
Declare @ errorsave int set @ errorsave = 0
Begin transaction outofproc
Print 'oa: @ error = '+ convert (varchar, @ error) +' | @ errorsave = '+ convert (varchar, @ errorsave)
Execute @ errorsave = ftransproc 6, 'aaa'
Print 'ob: @ error = '+ convert (varchar, @ error) +' | @ errorsave = '+ convert (varchar, @ errorsave)
Set @ errorsave = @ errorsave + @ Error
If (@ errorsave = 0) begin
Commit transaction outofproc
End else begin
Rollback transaction outofproc
End
Go
-------------------------------------------
-- Select * From testtrans
-- Delete from testtrans