Car Management App — TSQL.APP Guides

This example builds a small stock-and-sales app for a car dealer: a list of brands, a list of cars, a button to add a car, a button to mark one as sold, and a sales report. It shows the whole path — tables, screens, buttons — without leaving T-SQL.

1. Tables and cards in one call

A card is a TSQL.APP screen: a list and a form over one table or view. The quickest way to get both a table and its card is sp_api_create_table_card. You give it a JSON sample of the data; it creates the table in the business database, connects it to the application, registers the card with its fields, and — unless you ask for structure only — inserts the sample rows.

DECLARE @Brands NVARCHAR(MAX) = N'{"total": 4, "data": [
    {"id": 1, "name": "Volvo"}, {"id": 2, "name": "Toyota"},
    {"id": 3, "name": "Renault"}, {"id": 4, "name": "Kia"} ]}';

EXEC sp_api_create_table_card
     @TableName = N'car_brand'
    ,@IdColumn    = N'id'
    ,@UseIdentity = 1
    ,@TableJson   = @Brands;

DECLARE @Cars NVARCHAR(MAX) = N'{"total": 3, "data": [
    {"id": 1, "brand_id": 1, "model": "XC40",  "build_year": 2023, "price": 38950.50, "sold": false},
    {"id": 2, "brand_id": 2, "model": "Yaris", "build_year": 2022, "price": 17450.50, "sold": false},
    {"id": 3, "brand_id": 3, "model": "Clio",  "build_year": 2021, "price": 13900.50, "sold": true} ]}';

EXEC sp_api_create_table_card
     @TableName = N'car'
    ,@IdColumn    = N'id'
    ,@UseIdentity = 1
    ,@TableJson   = @Cars;

Column types are inferred from the first sample row: whole numbers become INT, numbers with a decimal point DECIMAL(18,2), true/false BIT, everything else NVARCHAR(MAX). Run the script once and two new cards, car_brand and car, appear in the menu.

Two details in the call are not optional:

Include the key column in the sample. The table's columns are taken from the keys of the first sample row, so a sample without id produces a table without an id column — and a card that cannot find its key.
@UseIdentity = 1 makes that key an IDENTITY primary key. The sample's own id values are then ignored and the rows are numbered as they are inserted, 1, 2, 3 — which is why the brand_id values in the car sample line up with the brands above.

sp_api_create_table_card replaces an existing table of the same name. Run it on a fresh name, never on a table that already holds data. For tables you already have, use sp_api_create_card instead, which only adds the card.

2. A button to add a car

Open the car card and add an action with this script. It offers the brands from car_brand as a picker and inserts the new car with a plain INSERT:

DECLARE @BrandId    NVARCHAR(MAX);
DECLARE @Model      NVARCHAR(MAX);
DECLARE @BuildYear  NVARCHAR(MAX);
DECLARE @Price      NVARCHAR(MAX);
DECLARE @SaveButton NVARCHAR(MAX);
DECLARE @YearNo     INT;
DECLARE @PriceNo    DECIMAL(18,2);
DECLARE @Message    NVARCHAR(MAX);

CREATE TABLE #Brand (id INT, name NVARCHAR(200));
INSERT INTO #Brand (id, name) SELECT id, name FROM car_brand;

EXEC sp_api_modal_get_value @name = N'@Model',      @value = @Model OUT;
EXEC sp_api_modal_get_value @name = N'@BuildYear',  @value = @BuildYear OUT;
EXEC sp_api_modal_get_value @name = N'@Price',      @value = @Price OUT;
EXEC sp_api_modal_get_value @name = N'@SaveButton', @value = @SaveButton OUT;

EXEC sp_api_modal_text @text = N'Add a car', @class = N'h3';
EXEC sp_api_modal_select_table @tmptable = N'#Brand', @name = N'@BrandId',
     @value = @BrandId OUT, @placeholder = N'Brand';
EXEC sp_api_modal_input @name = N'@Model',     @value = @Model OUT,     @placeholder = N'Model';
EXEC sp_api_modal_input @name = N'@BuildYear', @value = @BuildYear OUT, @placeholder = N'Build year';
EXEC sp_api_modal_input_decimal @name = N'@Price', @value = @Price OUT, @placeholder = N'Price';
EXEC sp_api_modal_button @name = N'@SaveButton', @value = N'Save',
     @valueout = @SaveButton OUT, @class = N'btn-primary';

IF @SaveButton IS NULL RETURN;

SET @YearNo  = TRY_CONVERT(INT, @BuildYear);
SET @PriceNo = TRY_CONVERT(DECIMAL(18,2), @Price);
IF @BrandId IS NULL OR LEN(TRIM(ISNULL(@Model, N''))) = 0
   OR @YearNo NOT BETWEEN 1950 AND 2100 OR ISNULL(@PriceNo, 0) <= 0
BEGIN
    SET @Message = N'Fill in brand, model, a build year and a price.';
    EXEC sp_api_toast @text = @Message, @class = N'bg-danger text-white';
    EXEC sp_api_modal_value @name = N'@SaveButton', @value = NULL;
    RETURN;
END

INSERT INTO car (brand_id, model, build_year, price, sold)
VALUES (TRY_CONVERT(INT, @BrandId), @Model, @YearNo, @PriceNo, 0);

SET @Message = CONCAT(@Model, N' added.');
EXEC sp_api_toast @text = @Message, @class = N'bg-success text-white';
EXEC sp_api_modal_restart @action_name = NULL, @modal_clear = 1, @refresh_all = 1;
RETURN;

Two things make this short. The table is reached by its plain name, car, because the card's table is connected to the application — no database prefix, no dynamic SQL. And sp_api_modal_restart with @refresh_all = 1 starts the form again, empty, ready for the next car, while also refreshing the list underneath so the new row shows up.

3. A button that works on the selected car

Every action script receives the selected record as @id. A Mark as sold action is therefore three statements:

DECLARE @Message NVARCHAR(MAX);

UPDATE car SET sold = 1 WHERE id = TRY_CONVERT(INT, @id) AND sold = 0;

SET @Message = IIF(@@ROWCOUNT = 1, N'Marked as sold.', N'This car was already sold.');
EXEC sp_api_toast @text = @Message;

Never declare @id yourself — it is set by the framework before your script starts. When the user has selected several rows, @ids holds them all as a comma-separated list.

4. A sales report

A report is a temporary table shown with sp_api_modal_table. Column names ending in * group the rows; names ending in @ are totalled. Ticking @excel adds a download button.

DECLARE @CloseButton NVARCHAR(MAX);

EXEC sp_api_modal_get_value @name = N'@CloseButton', @value = @CloseButton OUT;

SELECT [Brand*]     = b.name,
       [Model]      = c.model,
       [Build year] = c.build_year,
       [Price@]     = c.price
INTO #Sold
FROM car c
JOIN car_brand b ON b.id = c.brand_id
WHERE c.sold = 1;

EXEC sp_api_modal_text  @text = N'Cars sold', @class = N'h3';
EXEC sp_api_modal_table @tmptable = N'#Sold', @orderby = N'ORDER BY [Brand*], [Model]',
     @excel = 1, @print = 1;
EXEC sp_api_modal_button @name = N'@CloseButton', @value = N'Close',
     @valueout = @CloseButton OUT, @class = N'btn-outline-secondary';

IF @CloseButton IS NOT NULL EXEC sp_api_modal_clear;

What you did not have to write

No API layer, no front-end, no deployment step. The cards came from metadata generated by one procedure call; the buttons are rows of T-SQL. Change the car table, regenerate its fields, and the list and form follow. The Programmer's Manual covers cards, fields and drill-down navigation between them — the natural next step is a car list under each brand.