Which three Transact-SQL segments should you use to develop the solution?

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.

View Answer

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