How should you complete the procedure?

HOTSPOT
You are developing a stored procedure with the following requirements:
Accepts an integer as input and inserts the value into a table.
Ensures new transactions are committed as part of the outer transactions.
Preserves existing transactions if the transaction in the procedure fails. If the transaction in the procedure fails, rollback the transaction.
How should you complete the procedure? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Correct Answer:

Explanation/Reference:
Explanation:
Box 1: @TranCount> 0
Procedure called when there is an active transaction. Create a savepoint to be able to roll back only the work done in the procedure if there is an error.
Box 2: @TranCount = 0
— @TranCount = 0 means no transaction was started before the procedure was called. The procedure must commit the transaction it started.
Box 3: XACT_STATE() <> -1 If the transaction is still valid, just roll back to the savepoint set at the start of the stored procedure.
References: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/save-transaction-transact-sql?view=sql-server-2017