DP-800: Developing AI-Enabled Database Solutions

Question

You have an Azure SQL database with a table named dbo.Orders.

An application calls the dbo.usp_CreateOrder stored procedure to insert rows into dbo.Orders. When an insert fails, the application receives inconsistent error details.

You need to add error handling so failures within the procedure abort the transaction and return a consistent error to the caller. Complete the stored procedure. Each value may be used once, more than once, or not at all.

Drag & Drop

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, SYSDATETIME());

        ;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ;
        THROW;
    END CATCH
END

Explanation

SCOPE_IDENTITY() captures the identity generated by the order insert in the current stored-procedure scope. In the CATCH block, IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION safely reverses any active transaction, and THROW then preserves and returns the original error details to the caller.

Learn more

DP-800: Developing AI-Enabled Database Solutions

AstroTutor

Notes

Readiness

QuestionQ49

Community Discussion