Question
You have an Azure SQL database that contains a table named dbo.Orders.
You have an application that calls a stored procedure named dbo.usp_CreateOrder to insert rows into dbo.Orders.
When an insert fails, the application receives inconsistent error details.
You need to implement error handling to ensure that any failures inside the procedure abort the transaction and return a consistent error to the caller.
How should you complete the stored procedure? To answer, drag the appropriate values to the correct targets. Each value 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.
```sql
CREATE OR ALTER PROCEDURE dbo.usp_CreateOrder
@CustomerId int,
@Amount decimal(10,2),
@OrderId int OUTPUT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION;
INSERT INTO dbo.Orders(CustomerId, Amount, CreatedAt)
VALUES (@CustomerId, @Amount, SYSUTCDATETIME());
[Target 1];
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
[Target 2];
THROW;
END CATCH
END
```
Explanation
Target 1 is SET @OrderId = SCOPE_IDENTITY() because the procedure has an OrderId output parameter that must be filled before the commit, and SCOPE_IDENTITY returns the identity value inserted in the same scope. Target 2 is IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION because the catch block must abort the open transaction, and the check prevents a second error when no transaction is active, which an unconditional ROLLBACK TRANSACTION would raise. THROW without parameters then re-raises the caught exception with its original details, so the caller always receives the real error. RAISERROR is wrong here because it would replace those details and would not roll back the transaction.
