DRAG DROP
Note: This question is part of a series of questions that use the same scenario. For your convenience,
the scenario is repeated in each question. Each question presents a different goal and answer choices,
but the text of the scenario is exactly the same in each question in this series.
You are developing a database to track customer orders. The database contains the following tables:
Sales.Customers, Sales.Orders, and Sales.OrderLines. The following table describes the columns in
Sales.Customers.
The following table describes the columns in Sales.Orders.
The following table describes the columns in Sales.OrderLines.
You need to create a stored procedure that inserts data into the Customers table. The stored procedure must
meet the following requirements:
Data changes occur as a single unit of work.
Data modifications that are successful are committed and a value of 0 is returned.
Data modifications that are unsuccessful are rolled back. The exception severity level is set to 16 and a
value of -1 is returned.
The stored procedure uses a built-it scalar function to evaluate the current condition of data modifications.
The entire unit of work is terminated and rolled back if a run-time error occurs during execution of the stored
procedure.
How should complete the stored procedure definition? To answer, drag the appropriate Transact-SQL
segments to the correct targets. Each Transact-SQL segment may be used once, more than once, or not at all.
You may need to drag the split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.
Select and Place:
Answer: 
Explanation:
Box 1: XACT_ABORT
XACT_ABORT specifies whether SQL Server automatically rolls back the current transaction when a TransactSQL statement raises a run-time error.
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-timeerror, the entire transaction is
terminated and rolled back.
Box 2: COMMIT
Commit the transaction.Box 3: XACT_STATE
Box 4: ROLLBACK
Rollback the transaction
Box 5: THROW
THROW raises an exception and the severity is set to 16.
Requirement: Data modifications that are unsuccessful are rolled back. The exception severity level is set to 16
and a value of -1 is returned.https://msdn.microsoft.com/en-us/library/ms188792.aspx
https://msdn.microsoft.com/en-us/library/ee677615.aspx
correct
6
1
hello, the answer is not discordant with the question number 100120?
The roolback transaction you must not do if there is set xact_abort on
5
1
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql?view=sql-server-2017#c-using-trycatch-with-xact_state
xact_state() value can be
— If 1, the transaction is committable.
— If -1, the transaction is uncommittable and should
— be rolled back.
— XACT_STATE = 0 means that there is no transaction and
— a commit or rollback operation would generate an error.
so xact_state() 0 is ok followed by a rollback (since you are in the catch block, you got an error)
0
0
should’nt it be something like RAISERROR(‘some message’,16,1)? Because THROW will terminate execution of the stored procedure, so -1 will not be returned.
4
0
A THROW statement terminates the entire batch (unless handled with an outer exception handler) so there is not need for the RETURN after THROW. or better ;THROW
it exits by default with severity 16. this is a requirement. but we miss the return -1
by the way: raiserror is not enought , it needs parameters.
i would choose ;THROW as option.
2
0