Exam 70-761 All Questions

View all questions & answers for the 70-761 exam

Exam 70-761 topic 1 question 52 discussion

Actual exam question from Microsoft's 70-761

Question #: 52
Topic #: 1

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 to the calling procedure.

✑ Data modifications that are unsuccessful are rolled back. You must display a message that uses severity level 16 and a value of -1.
✑ The stored procedure uses a built-in 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:

Show Suggested Answer

Suggested Answer:

Box 1: XACT_ABORT -
XACT_ABORT specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL 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.
References:
https://msdn.microsoft.com/en-us/library/ms188792.aspx
https://msdn.microsoft.com/en-us/library/ee677615.aspx

by Abhilash_KK at Sept. 4, 2019, 11:28 a.m.

Comments

Submit Cancel

Abhilash_KK

Highly Voted 5 years, 9 months ago

a line terminator ; is required before THROW keyword, otherwise it will throw syntax error

upvoted 13 times

Anette

5 years, 1 month ago

YES ; is needed in the end of Print statement

upvoted 2 times

...

PHaringsNL

Most Recent 4 years, 5 months ago

CREATE PROCEDURE InsertCustomer @CustomerName nvarchar(100), @PhoneNumber nvarchar(20), @AccountOpenedDate date, @StandardDiscountPercentage decimal(18,3), @CreditLimit decimal(18,2), @IsCreditOnHold bit, @DeliveryLongitude nvarchar(50), @DeliveryLatitude nvarchar(50) AS BEGIN SET NOCOUNT ON SET XACT_ABORT ON BEGIN TRY BEGIN TRANSACTION INSERT INTO Customer(CustomerName,PhoneNumber,AccountOpenedDate,StandardDiscountPercentage, CreditLimit,IsCreditOnHold, DeliveryLocation) VALUES (@CustomerName,@PhoneNumber,@AccountOpenedDate, @StandardDiscountPercentage, @CreditLimit,@IsCreditOnHold, geography::Point(ISNULL(@DeliveryLongitude, ''), ISNULL(@DeliveryLatitude, ''), 4326)) COMMIT TRANSACTION END TRY BEGIN CATCH IF XACT_STATE () <> 0 ROLLBACK TRANSACTION PRINT 'Unable to create the customer record.'; THROW; RETURN -1 END CATCH RETURN 0 END

upvoted 2 times

...

PHaringsNL

4 years, 5 months ago

Tested answer: DROP TABLE Customer CREATE TABLE Customer ( CustomerName nvarchar(100) NOT NULL, PhoneNumber nvarchar(20) NULL, AccountOpenedDate date NOT NULL, StandardDiscountPercentage decimal(18,3) NULL, CreditLimit decimal(18,2) NULL, IsCreditOnHold bit NOT NULL, DeliveryLocation geography NOT NULL) INSERT INTO Customer VALUES ('Person Name', '0612345678', '2019-01-05', 15, 10000, 0, '145125255'), ('Someone Lastname', '0698765432', '2019-07-15', 6, 0, 1000, '145752755') Continue next comment....

upvoted 1 times

...

eggzamtaker

4 years, 5 months ago

Why are so many ppl commenting about things that have NOTHING to do with the freaking answer? Who cares that there's "return -1" at the very end. In no way, shape, or form, does that dictate the answer selections. Stop wasting people's time.

upvoted 1 times

...

Vermonster

4 years, 5 months ago

Agreed that everything is correct except that there is no possible way to return -1 becaue THROW would end the CATCH and RAISERROR as a statement with no parameters wouldn't work

upvoted 1 times

...

Hoglet

4 years, 6 months ago

THROW in this context won't work for several reasons. 1. THROW with no arguments in a CATCH block is re-throwing the original exception, with that exceptions Severity 2. Control is returned to the caller, so the "RETURN -1" will not be called While not possible to call RAISERROR by itself, with the 2 issues pointed out, it has to be correct answer. If THROW were used with additional arguments to cause a Level 16 to be returned, we still wouldn't call the RETURN -1

upvoted 1 times

...

ZSQL

4 years, 8 months ago

I believe throw is the last statement in catch block to raise an error and exit the catch block , no other statement will work, how come return -1 statement will work here ???

upvoted 2 times

...

Jangstar

4 years, 9 months ago

Probably want to set XACT_ABORT as OFF since we can't consistently THROW the desired error. Once XACT_ABORT is ON then any run-time error will end the execution of code.

upvoted 1 times

...

vermeilyn

5 years, 1 month ago

You are not using @@Transcount because it doesn't have parentheses behind. Only XACT_STATE () does.

upvoted 3 times

vermeilyn

5 years, 1 month ago

Also when XACT_ABORT is on, it dooms the transaction, so @@transcount can't tell you whether the transaction is doomed or committed. You need XACT_STATE to tell you that

upvoted 3 times

...

Barbedx

5 years, 4 months ago

there are need to be a @@Trancount, not XACT_STATE

upvoted 2 times

matija1

5 years, 4 months ago

No, different from 0 (<>) is XCAT_STATE while > 0 is @@Trancount

upvoted 1 times

...

daniel_yes23

5 years, 2 months ago

XACT_STATE is correct. https://docs.microsoft.com/en-us/sql/t-sql/functions/trancount-transact-sql?view=sql-server-ver15

upvoted 2 times

...

Anette

5 years, 1 month ago

if Rollback, @@TRANCOUNT is 0 not <>0. SO it is XACT_STATE

upvoted 1 times

...

BabyBee

4 years, 7 months ago

The key is here "✑ The stored procedure uses a built-in scalar function to evaluate the current condition of data modifications.". XACT_STATE() is a built-in function, @@TRANCOUNT is a variable. ;)

upvoted 4 times

...

Load full discussion...

...