Execute

Calling a stored procedure, in three scenarios: named arguments; positional arguments; a parameter left out, so the procedure's default is used. The call is described with ArxProcedureExpression, the way a query is described with ArxQueryExpression; ARX sends EXEC with every value as a parameter.

OUT and INOUT values, in two scenarios: an INOUT goes in and comes back (here filled in by the procedure when it arrives as NULL), and OUT values come back typed in Outputs. SQL Server has one kind, OUTPUT, which is both: ARX sends an OUT as an OUTPUT parameter that starts as NULL. Each is declared with its .NET type, and ARX sizes it to hold any value of that type.

Calling a function, in three scenarios: one value (Value<T>()); rows (ResultSets[0]); and an effect, which on SQL Server is a procedure's job. A function is the same ArxProcedureExpression with Function: ARX sends SELECT * FROM fn(…) for a table-valued function and SELECT fn(…) for a scalar one, instead of EXEC. To filter, order or page a function's rows like a table, Retrieve reads it as a row source (RetrieveMultiple's RealCase6, Advanced8).

A procedure that returns tables, in two scenarios. A SQL Server procedure returns a table by selecting it: each SELECT it runs is a result set. ARX runs the call in one round trip and returns the result sets by position (ResultSets[0], ResultSets[1] …), in the order the procedure sends them.

Placing an order through a procedure, in two scenarios: sp_place_order writes the order and its lines (from the products' current prices) in one call and returns the new id and the total; the id is then used like any other (Retrieve reads the order back). The application doesn't repeat the pricing rule the procedure owns.

An account page built from one call: sp_account_report returns three tables (the contacts, the orders, a summary row), read together in one round trip, each by its position: the order of the procedure's SELECTs.

Paging what a procedure or function returns, the three ways, in four scenarios. Each sets PageInfo, as a query does, and gets back a result set with CurrentPage, HasNextPage and, when asked, the total. The total is opt-in: ask for it once (page 1), as for RetrieveMultiple.

1. The routine pages itself (best for large data: the database produces only the page). ARX fills its page arguments from PageInfo and reads its total from an OUT parameter.
2. ARX pages a function's rows: OFFSET/FETCH NEXT and the total in one round trip. Cheap for an inline table-valued function (SQL Server expands it, so the order and the limit reach the index); a multi-statement function fills its whole table first.
3. ARX pages a table the procedure returns (a result set): SQL Server has no cursor to move, so ARX reads past the earlier rows, keeps one page and one row more, and counts the rest. Every row crosses the network.
4. Which to choose.

Business rules the database enforces, in three scenarios. sp_reserve_credit refuses an account on hold (THROW 50101) and an amount over the credit limit (THROW 50102). Both arrive as ARX3005 RaisedByDatabase with the procedure's message and its error number as NativeCode, so the application can tell "the database said no" from "something broke", and branch on the procedure's own number.

A nightly job that commits as it goes, in two scenarios. sp_recalculate_totals recalculates order totals in batches and commits after each, so a failure late in the run keeps the batches already done, and reports its progress. ARX's own call runs outside any transaction, which a procedure that commits needs (inside one, SQL Server ends the caller's transaction at the procedure's COMMIT and reports error 266, a changed transaction count); inside an ARX transaction or a batch scope the same call is refused with ARX8001 and the reason.

The messages a procedure sends while it runs, in two scenarios: PRINT and RAISERROR of severity 10 or less (information, not errors; SQL Server reports their severity as 0) come back in Messages, in order, for the application to show or log as it chooses (ARX never writes them to a log); CollectMessages = false leaves them out.

The errors an Execute call can raise, turned into messages, in two scenarios: what the database refuses (a procedure that doesn't exist, arguments of the wrong type, a function called as a procedure, a timeout) and what ARX refuses before it (a positional argument after a named one, an OUT parameter without a type, a cursor parameter).

Catching an SQL injection attempt on Execute, in three scenarios. A routine name or a parameter name that carries SQL is refused with ARX1017 SuspectedInjection before the database. SQL in a value is a parameter: it's stored as text and runs nothing. Execute never takes SQL text.

Execute inside an ARX transaction, in two scenarios: a Create and a procedure in one transaction, the procedure's writes read back inside it, and everything gone after a rollback; and a business error from the procedure aborting the transaction, so the next call is refused (ARX1015) until the application rolls back.

Execute in a batch scope, in one scenario: an import of three orders through the procedure, one transaction. Each call has its own savepoint (asked for with Savepoints = true), so the order the procedure refuses (a credit limit) is undone alone and the import continues; the others are committed together at the end.

Exporting everything a procedure returns, page by page, in one scenario: sp_products_page pages itself (the first way of paging), the export asks for the total on the first page only, and reads pages until HasNextPage is false. Each page is one call, so the memory is one page's.

A security check around Execute (SecurityGuard, in the project root), in three scenarios: a report run through an allowed list of procedures passes; an endpoint that runs whatever procedure the request names is probed, and the attacker is blocked after three attempts; and a false positive (an old client sending "account report (v1)") is triaged: support unblocks the user and the endpoint maps the old name.

Execute on a service whose schema is preloaded and Strict, in three scenarios: a procedure and a function called as usual, because Strict covers tables and views, not routines; a function read as a row source, with the routine's own columns; and the same service still refusing a table column that wasn't preloaded.