Exam 70-762 topic 1 question 154 discussion

DRAG DROP -
You have a database that contains three encrypted store procedures named dbo.Proc1, dbo.Proc2 and dbo.Proc3. The stored procedures include INSERT,
UPDATE, and DELETE statements.
You have the following requirements:
✑ You must run all the stored procedures within the same transaction.
✑ You must automatically start a transaction when stored procedures include DML statements.
✑ You must not automatically start a transaction when stored procedures include DDL statements.
You need to run all three stored procedures.
Which three Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments to the answer area and arrange then in the correct order.
Select and Place:

Suggested Answer: Explanation
Step 1: SET IMPLICIT_TRANSACTIONS ON BEGIN TRAN
When ON, the system is in implicit transaction mode. This means that if @@TRANCOUNT = 0, any of the following Transact-SQL statements begins a new transaction. It is equivalent to an unseen BEGIN TRANSACTION being executed first:

Step 2:
Run the procedure within the same transaction
Step 3:

Commit the transaction -
References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-implicit-transactions-transact-sql