There is no await in T-SQL
Before a database can host an interactive application at all, one problem has to be
solved. A T-SQL batch runs and finishes. It holds a connection and, in TSQL.APP, an open
transaction. It cannot sit at line 14 for forty seconds while a warehouse operator
decides which pallet to scan. There is no await in T-SQL, and there is no sane way to
invent one: a language with no continuations, running on a connection you do not own,
cannot be suspended halfway.
Every framework that puts a UI in front of a procedure has to answer this. Most answer it with server-side session state — a run id, a step counter, a serialised object graph parked in a table or a cache, reloaded on the next request.
TSQL.APP answers it by not suspending anything. The script is re-executed from line 1 on every round trip, and there is no resume point at all.
A real script
This is a complete, unedited action script from the framework's own card settings — it sets the row limit on a list view:
sqlEXEC sp_api_modal_text N'Set Record Limit & Threshold', N'h4 text-muted'
EXEC sp_api_modal_input @name='@limit', @value = @limit OUT, @focus=1
EXEC sp_api_modal_input @name='@threshold', @value = @threshold OUT
EXEC sp_api_modal_button @name='@button', @value='Set', @valueout = @button OUT, @key='Enter'
IF @button IS NULL RETURN -- <<< the pass ends here
UPDATE api_card SET record_limit = TRY_CAST(@limit AS int) WHERE id IN({@ids})
UPDATE api_card SET record_threshold = TRY_CAST(@threshold AS int) WHERE id IN({@ids})
EXEC sp_api_modal_clear
Read it as an ordinary batch, because that is exactly what it is.
On the first request the three render calls queue their elements and the script hits
RETURN. sp_api_modal_input never aborts, never waits, never throws — it renders and
returns. And the guard clause does not pause the script either. It ends it. The batch
completes, the transaction closes, the connection goes back to the pool, and for as long
as the user is reading the screen there is nothing of this script alive on the server at
all.
The modal ships to the browser. The user types two numbers and presses Enter, and the
same batch runs again, top to bottom. The same three lines execute a second time — but
this time they fill their OUT variables from the answers that came back with the
request, @button is no longer NULL, the guard falls through, and the UPDATEs run.
One script. Two executions. No continuation.
That distinction is worth holding onto, because the intuition it displaces is a strong one. It feels like the script is waiting at the gate — like line 14 is parked there, holding the user's place. Nothing is parked. The pass that drew the modal ran to the gate and ended. Its variables are gone and its temp tables are gone.
The framework has one convention that makes this unusually literal. A script can call
another action as a subroutine by writing EXEC #name, and that line is not what it
looks like: at save time the platform rewrites the script to prepend generator code, so
at runtime the batch reads the named body out of the action library, builds a #-scoped
stored procedure from it, and calls it. A real procedure, created on the spot. Its
lifetime is the batch. It is dropped when the pass ends, and the next pass builds it
again from scratch.
So even in the one case where a stored procedure genuinely exists at runtime, it exists for milliseconds, and it is gone long before the human has focused on the screen. By the time the operator reaches for the keyboard, no part of that execution is left anywhere.
The user, then, is never answering a running script. They are answering the output of one that finished milliseconds after it started, and their answer is handed to a new execution of the same text: one that will re-run every line its predecessor ran, and then stop one gate further along.
Not one script suspended at five points, then. Six executions of one script, each ending at the first question it cannot answer out of the bag.
Where the state actually lives
Not in a table. It lives in the HTTP body and is re-sent by the browser on every step. Server-side it arrives as a single row:
json{
"type": "stored_procedure",
"name": "<action name>",
"id": 42,
"values": { "@limit": "25", "@threshold": "500" }
}
values is the bag, and it is shredded once per request into a temp table that one
procedure reads. Each collector then prefers the posted answer over its own input. Here
is the whole of that primitive, verbatim from sp_api_modal_button:
sqlEXEC sp_api_modal_get_value @name, @value = @postvalue OUT
IF @postvalue IS NOT NULL
BEGIN
SET @valueout = @postvalue
END
That is the flow control. Before the click, @valueout is NULL. After it, the caption.
Every IF in every wizard in the platform hangs off that six-line pattern.
The part worth knowing
Look at what is used as the key in that bag: the element's name. Not a run id, not an action id, not a step index. There is no step counter anywhere in the key space — you can search for one and you will not find it.
Which means step N is not stored. It is emergent: step N is the first collector whose name is not yet in the bag. A five-step wizard is one linear script with five guards in it, and "where am I" is answered by looking at what has already been answered.
Once that lands, three things stop being surprising and start being obvious.
Names are your primary key. Two sp_api_modal_input @name='@x' in one script share
one answer, because they share one key. That is not a bug to be fixed, it is the model
working as designed — and it is why the collectors take @identifier and @seed. A loop
that renders one input per row needs one distinct name per row, or every row shows the
same value.
Everything above your first unanswered guard runs again on every pass. An INSERT
or an email send sitting above the guard in a five-step dialog fires five times. Writes
go below the last guard, or they are made idempotent. There is no third option.
sp_api_modal_clear belongs at the top of a render pass, or the replay stacks this
pass's elements underneath the previous pass's.
None of these are quirks. They are all the same fact, seen from three sides.
The other half of the loop
The browser's side is just as literal. Along with the modal, the server echoes the request body straight back. The client re-hydrates the bag from that echo, overlays the current value of every element it just rendered, and re-posts the whole accumulated object to the same URL — the browser URL is the API URL, so there is no separate modal endpoint to speak of.
So the accumulated answer set is a client-held object, seeded by the server, incremented by one answer per step, and posted whole every time. That is the entire coroutine — and there is no server-side session behind a wizard to expire, leak between two people running the same action, or need collecting when they walk away from it.
What it costs you
It would be dishonest to present this as free.
You give up the ability to write a long imperative procedure that reads top to bottom as one uninterrupted story. You have to think about idempotence every time you write above a guard. And the transaction is per request, not per wizard — an error on step four does not undo what step two committed, which is the subject of another post.
What you get back is a model with no resume state to corrupt, because there is no resume state. A retry re-derives the whole situation from the bag and the database, every time. For a barcode scanner in a warehouse — where the request that goes missing is the normal case rather than the exceptional one — that trade pays for itself repeatedly.
The programming reference has the full modal vocabulary, the documentation covers the rest of the framework, and there is a demo if you would rather watch it happen.