TSQL.APP — Technical Training

1The shape of the system

Most stacks spread a feature across five technologies. TSQL.APP collapses that: the database is the application. The other two layers are deliberately incapable of holding business logic.

2The wire: one table in, named datasets out

Inbound: everything becomes rows

All four HTTP verbs are registered on one catch-all route. The request — path, query string, headers, cookies, body, uploaded files, and every JWT claim — is flattened into a single table-valued parameter and passed to one stored procedure.

dbo.api_main_context_table (id int, grp nvarchar(128), k nvarchar(128), v nvarchar(max))

grp ∈ claim | route | query | header | cookie | body | file | raw | version | database

Route segments are paired, not parsed: /order/42/line/7 becomes (route,order,42) and (route,line,7). There is no URL grammar anywhere in the system.

Outbound: a marker row names the next result set

The procedure returns many result sets. The API treats a result set as a name tag for the one that follows it when it has exactly one row, at most four columns, and a column called dataset.

SELECT dataset='data'      -- marker: "the next result set is called data"
SELECT id, name, amount FROM ...   -- the payload

SELECT dataset='toasts'
SELECT * FROM #toasts

3The kernel: what sp_api_start does for you

Before your action script executes, the kernel has already built the response buffer. These temp tables exist for the life of the request, and writing to them is how you produce output:

Also already done for you: the bearer token has been scrubbed out of #context, every ?foo=bar is in SESSION_CONTEXT('query_foo'), claims are folded into user_claims and user_roles, and the business database name is derived by a single string slice:

@main_database = SUBSTRING(DB_NAME(), 0, CHARINDEX('_proj', DB_NAME(), 0))
-- app_proj → app.  That one line is the entire multi-tenant model.

4Routes are a recursion

There is no routing table. sp_api_card_list takes the next route row from #context, resolves it against api_card, and calls itself for the segment after it. Tabs, breadcrumbs and data scoping all fall out of that one recursion.

/order/42/line/7/allocation
   │      │    │  │      └── no id → Listview (the leaf)
   │      │    │  └───────── id 7  → Detailview, then recurse
   │      │    └──────────── card "line", bound to parent via api_card_children.ref
   │      └───────────────── id 42 → Detailview, then recurse
   └──────────────────────── card "order"

The card lookup applies the ACL inline:

FROM api_card ac CROSS APPLY tvf_hasRole_v3(ac.role, ac.deny_role)

5The screenplay: replay, not coroutines

This is the mechanism that surprises everyone, including AI agents asked to describe it. A script can ask the user a question and appear to carry on afterwards. SQL Server has no coroutines. So how?

A two-step button, and what actually happens

EXEC sp_api_modal_text  N'Set Record Limit', N'h4 text-muted'
EXEC sp_api_modal_input  @name='@limit', @value = @limit OUT, @focus=1
EXEC sp_api_modal_button @name='@button', @value='Set', @valueout = @button OUT, @key='Enter'

IF @button IS NULL RETURN            -- ← the "await". Your code, not the framework's.

UPDATE api_card SET record_limit = TRY_CAST(@limit AS int) WHERE id IN ({@ids})
EXEC sp_api_modal_clear

Why the collectors cooperate

sp_api_modal_input and sp_api_modal_button never abort. They render, then prefer the posted answer over their input:

EXEC sp_api_modal_get_value @name, @value = @postvalue OUT
IF @postvalue is not null
    BEGIN
        SET @valueout = @postvalue
    END

Before the click @valueout is NULL; after, it holds the caption. The suspension is entirely your IF … RETURN.

Two more consequences

• Names are the primary key of your state. Two sp_api_modal_input @name='@x' in one script share one answer. Disambiguate with @identifier or @seed — that is exactly how loops over rows work.
• The transaction is per request, not per wizard. A mid-script error discards this request's writes; anything committed on an earlier step is already durable.

6The subroutine convention: EXEC #name

A button body normally runs as a batch — EXEC sp_executesql @sql, … — not as a procedure. But a script can call another action as a subroutine by writing EXEC #name, and that changes the picture: such a script is rewritten so that at runtime it dynamically generates and runs a temporary stored procedure.

Three facts that are easy to get wrong

• Helper actions are api_actions rows whose name begins with #. The hash is part of the stored name, because the scanner slices it out of your script text and looks it up verbatim. A mature app typically grows a small library of them — #alert, #h2, #report_footer, #lookup, #ledger.
• A temp procedure does not inherit the caller's parameters, so the generator re-derives @user_name, @user_id, @card_name, @parent_card_name and @parent_parent_id from SESSION_CONTEXT inside the generated body.
• It re-runs on every replay step (Module 5). Drop and rebuild each round trip, then it survives for the rest of that batch.

Three different moments build three different procedures

The button body itself is never any of these. It is the batch.

7Transactions and the severity channel

One transaction wraps the whole request, including the entire replayed script. Severity is used as a control-flow channel, not as an error level:

8Lab: prove it yourself

Read a procedure in full (-y 0 prevents truncation; note -h and -y are mutually exclusive in sqlcmd):

sqlcmd -S <server> -d <yourapp>_proj -y 0 \
       -Q "SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.sp_api_card_actions_execute'))"

The five sentences worth memorising

1. The database is the application; the API and the client cannot hold logic.
2. One table goes in, named result sets come out — and an unlabelled SELECT becomes data.
3. An action script is replayed from the top on every round trip; its state travels through the browser.
4. Put your writes after the final guard, or make them idempotent.
5. EXEC #name means the batch builds and runs a temp procedure — and compile-clean is not correct.