Multi-Step Pizza Ordering

Long forms work better in steps. This wizard takes the Pizza Time order and splits it into three screens — choose, deliver, confirm — with Next and Back buttons.

There is no server-side session holding the wizard's position. The current step is just another value, @Step, carried from one run to the next by sp_api_modal_restart.

The script

-- Variables
DECLARE @Step        NVARCHAR(MAX);
DECLARE @PizzaId     NVARCHAR(MAX);
DECLARE @Size        NVARCHAR(MAX);
DECLARE @Address     NVARCHAR(MAX);
DECLARE @Phone       NVARCHAR(MAX);
DECLARE @NextButton  NVARCHAR(MAX);
DECLARE @BackButton  NVARCHAR(MAX);
DECLARE @StepNo      INT;
DECLARE @PizzaName   NVARCHAR(100);
DECLARE @Heading     NVARCHAR(MAX);
DECLARE @Summary     NVARCHAR(MAX);
DECLARE @Message     NVARCHAR(MAX);
DECLARE @Json        NVARCHAR(MAX);

CREATE TABLE #Menu (id INT, name NVARCHAR(100));
INSERT INTO #Menu (id, name) VALUES
    (1, N'Margherita'), (2, N'Funghi'), (3, N'Quattro Formaggi'), (4, N'Diavola');

-- Synchronise everything the wizard carries
EXEC sp_api_modal_get_value @name = N'@Step',       @value = @Step OUT;
EXEC sp_api_modal_get_value @name = N'@Address',    @value = @Address OUT;
EXEC sp_api_modal_get_value @name = N'@Phone',      @value = @Phone OUT;
EXEC sp_api_modal_get_value @name = N'@NextButton', @value = @NextButton OUT;
EXEC sp_api_modal_get_value @name = N'@BackButton', @value = @BackButton OUT;
-- Values carried by a restart (pickers on step 1 read their own)
IF @PizzaId IS NULL EXEC sp_api_modal_get_value @name = N'@CarriedPizzaId', @value = @PizzaId OUT;
IF @Size    IS NULL EXEC sp_api_modal_get_value @name = N'@CarriedSize',    @value = @Size OUT;

SET @StepNo = ISNULL(TRY_CONVERT(INT, @Step), 1);
SET @Heading = CONCAT(N'Order a pizza — step ', @StepNo, N' of 3');
EXEC sp_api_modal_text @text = @Heading, @class = N'h4';

------------------------------------------------------------------ step 1: choose
IF @StepNo = 1
BEGIN
    EXEC sp_api_modal_select_table @tmptable = N'#Menu', @name = N'@PizzaId',
         @value = @PizzaId OUT, @initial_id = @PizzaId, @placeholder = N'Choose a pizza';
    EXEC sp_api_modal_choose @list = N'Small,Medium,Large', @name = N'@Size', @value = @Size OUT;
    EXEC sp_api_modal_button @name = N'@NextButton', @value = N'Next',
         @valueout = @NextButton OUT, @class = N'btn-primary';
    IF @NextButton IS NULL RETURN;

    IF @PizzaId IS NULL OR @Size IS NULL
    BEGIN
        SET @Message = N'Choose a pizza and a size.';
        EXEC sp_api_toast @text = @Message, @class = N'bg-danger text-white';
        EXEC sp_api_modal_value @name = N'@NextButton', @value = NULL;
        RETURN;
    END
    SET @Json = (SELECT [@Step] = 2, [@CarriedPizzaId] = @PizzaId, [@CarriedSize] = @Size,
                        [@Address] = @Address, [@Phone] = @Phone
                 FOR JSON PATH, WITHOUT_ARRAY_WRAPPER);
    EXEC sp_api_modal_restart @values = @Json;
    RETURN;
END

------------------------------------------------------------------ step 2: deliver
IF @StepNo = 2
BEGIN
    EXEC sp_api_modal_input @name = N'@Address', @value = @Address OUT, @placeholder = N'Delivery address';
    EXEC sp_api_modal_input @name = N'@Phone',   @value = @Phone OUT,   @placeholder = N'Phone number';
    EXEC sp_api_modal_button @name = N'@BackButton', @value = N'Back',
         @valueout = @BackButton OUT, @class = N'btn-outline-secondary';
    EXEC sp_api_modal_button @name = N'@NextButton', @value = N'Next',
         @valueout = @NextButton OUT, @class = N'btn-primary';
    IF @NextButton IS NULL AND @BackButton IS NULL RETURN;

    IF @NextButton IS NOT NULL AND (LEN(TRIM(ISNULL(@Address, N''))) < 5 OR LEN(TRIM(ISNULL(@Phone, N''))) < 6)
    BEGIN
        SET @Message = N'Enter a delivery address and a phone number.';
        EXEC sp_api_toast @text = @Message, @class = N'bg-danger text-white';
        EXEC sp_api_modal_value @name = N'@NextButton', @value = NULL;
        RETURN;
    END
    SET @Json = (SELECT [@Step] = IIF(@NextButton IS NOT NULL, 3, 1),
                        [@CarriedPizzaId] = @PizzaId, [@CarriedSize] = @Size,
                        [@Address] = @Address, [@Phone] = @Phone
                 FOR JSON PATH, WITHOUT_ARRAY_WRAPPER);
    EXEC sp_api_modal_restart @values = @Json;
    RETURN;
END

------------------------------------------------------------------ step 3: confirm
SELECT @PizzaName = name FROM #Menu WHERE id = TRY_CONVERT(INT, @PizzaId);
SET @Summary = CONCAT(@Size, N' ', @PizzaName, N' to ', @Address, N' (', @Phone, N')');
EXEC sp_api_modal_text @text = @Summary;
EXEC sp_api_modal_button @name = N'@BackButton', @value = N'Back',
     @valueout = @BackButton OUT, @class = N'btn-outline-secondary';
EXEC sp_api_modal_button @name = N'@NextButton', @value = N'Place order',
     @valueout = @NextButton OUT, @class = N'btn-primary';
IF @NextButton IS NULL AND @BackButton IS NULL RETURN;

IF @BackButton IS NOT NULL
BEGIN
    SET @Json = (SELECT [@Step] = 2, [@CarriedPizzaId] = @PizzaId, [@CarriedSize] = @Size,
                        [@Address] = @Address, [@Phone] = @Phone
                 FOR JSON PATH, WITHOUT_ARRAY_WRAPPER);
    EXEC sp_api_modal_restart @values = @Json;
    RETURN;
END

-- In a real app: INSERT INTO pizza_order (...) VALUES (...);
EXEC sp_api_modal_clear @alert_text = N'Order placed', @sub_text = @Summary;

Why it is built this way

Every transition is a restart. Moving between steps calls sp_api_modal_restart with a JSON object of everything the wizard knows so far. The restarted run reads those values back with sp_api_modal_get_value, so each step starts with a clean screen but a full memory.

Back is the same move as Next. Back is just a restart with a lower @Step. Because the address and phone travel in the JSON in both directions, going back and forward again keeps what the user typed.

Carried values have their own names. The pizza and size are chosen with pickers on step 1. Pickers read their own posted value, so on later steps the wizard carries them under different names, @CarriedPizzaId and @CarriedSize. On step 1 itself, @initial_id puts the earlier choice back into the picker when the user returns with Back.

Validation only blocks forward. On step 2, Back is always allowed; Next checks the input first, and resets its own button if the check fails.

Nothing is written until the end. Steps 1 and 2 change no data, so abandoning the wizard halfway leaves nothing behind. The single INSERT belongs on the confirm step.