Pizza Time App

This example builds a small but complete order form: pick a pizza, choose a size, add extra cheese, set a quantity, and place the order. It uses five different controls and shows how each one gets its value back on the next run.

The menu lives in a temporary table so the example runs anywhere. In a real application it would be a SELECT from your own products table.

The script

-- Variables
DECLARE @PizzaId      NVARCHAR(MAX);
DECLARE @Size         NVARCHAR(MAX);
DECLARE @ExtraCheese  NVARCHAR(MAX);
DECLARE @CheeseBit    BIT;
DECLARE @Quantity     NVARCHAR(MAX);
DECLARE @OrderButton  NVARCHAR(MAX);
DECLARE @Qty          INT;
DECLARE @PizzaName    NVARCHAR(100);
DECLARE @UnitPrice    DECIMAL(6,2);
DECLARE @Total        DECIMAL(8,2);
DECLARE @Message      NVARCHAR(MAX);

-- The menu (id + name is what a picker needs; price is ours)
CREATE TABLE #Menu (id INT, name NVARCHAR(100), price DECIMAL(6,2));
INSERT INTO #Menu (id, name, price) VALUES
    (1, N'Margherita', 9.50), (2, N'Funghi', 11.00),
    (3, N'Quattro Formaggi', 12.50), (4, N'Diavola', 12.00);

-- Synchronise plain inputs and the button (pickers sync themselves)
EXEC sp_api_modal_get_value @name = N'@Quantity',    @value = @Quantity OUT;
EXEC sp_api_modal_get_value @name = N'@OrderButton', @value = @OrderButton OUT;

-- Draw
EXEC sp_api_modal_text @text = N'Pizza Time', @class = N'h3';

EXEC sp_api_modal_text @text = N'Pizza', @class = N'fw-bold';
EXEC sp_api_modal_select_table @tmptable = N'#Menu', @name = N'@PizzaId',
     @value = @PizzaId OUT, @placeholder = N'Choose a pizza';

EXEC sp_api_modal_text @text = N'Size', @class = N'fw-bold';
EXEC sp_api_modal_choose @list = N'Small,Medium,Large', @name = N'@Size', @value = @Size OUT;

EXEC sp_api_modal_switch @name = N'@ExtraCheese', @value = @ExtraCheese OUT,
     @bitvalue = @CheeseBit OUT, @label = N'Extra cheese (+ 1.50)';

EXEC sp_api_modal_input @name = N'@Quantity', @value = @Quantity OUT, @placeholder = N'Quantity';

EXEC sp_api_modal_button @name = N'@OrderButton', @value = N'Place order',
     @valueout = @OrderButton OUT, @class = N'btn-primary';

-- Wait for the click
IF @OrderButton IS NULL RETURN;

-- Validate
SET @Qty = TRY_CONVERT(INT, @Quantity);
IF @PizzaId IS NULL OR @Size IS NULL OR @Qty IS NULL OR @Qty NOT BETWEEN 1 AND 20
BEGIN
    SET @Message = N'Choose a pizza and a size, and order between 1 and 20.';
    EXEC sp_api_toast @text = @Message, @class = N'bg-danger text-white';
    EXEC sp_api_modal_value @name = N'@OrderButton', @value = NULL;
    RETURN;
END

-- Price it
SELECT @PizzaName = name, @UnitPrice = price FROM #Menu WHERE id = TRY_CONVERT(INT, @PizzaId);
SET @UnitPrice += CASE @Size WHEN N'Small' THEN -2.00 WHEN N'Large' THEN 3.00 ELSE 0 END
                + IIF(@CheeseBit = 1, 1.50, 0);
SET @Total = @UnitPrice * @Qty;

-- In a real app: INSERT INTO pizza_order (...) VALUES (...);

SET @Message = CONCAT(@Qty, N' × ', @Size, N' ', @PizzaName,
                      IIF(@CheeseBit = 1, N' with extra cheese', N''),
                      N' — total ', FORMAT(@Total, N'0.00'));
EXEC sp_api_modal_clear @alert_text = N'Order placed', @sub_text = @Message;

How the pieces fit

Two kinds of control, two ways to sync. Plain inputs and buttons are read with sp_api_modal_get_value before anything is drawn. Pickers — sp_api_modal_select_table, sp_api_modal_choose and the switch — read their own posted value when they are drawn and hand it back through their OUT parameter. That is why @PizzaId, @Size and @CheeseBit have no sp_api_modal_get_value line.

A picker needs id and name. sp_api_modal_select_table shows the name column and returns the id of the chosen row. Any extra columns — here, price — are ignored by the picker but available to you.

The switch returns two things. @value is the posted text; @bitvalue is the same choice as a BIT, which is what you want for arithmetic.

The temp table is created once, at the top. The script runs again on every click, so #Menu is rebuilt every time — cheap, and it guarantees the picker and the price lookup see the same data.

Validation un-clicks the button. If anything is missing, the toast explains it and sp_api_modal_value resets @OrderButton, so the form waits for the user instead of failing again.

Closing with a message. sp_api_modal_clear closes the dialog; given @alert_text and @sub_text, it shows a confirmation as it does so.