# Execute with ARX.SDK on SQL Server

## What an Execute does

| | |
|---|---|
| **The call** | `new ArxProcedureExpression("sp_place_order")`, in the pattern of `ArxQueryExpression`: `AddParameter`, `AddOutParameter<T>`, `AddInOutParameter<T>`; `ProcedureType = ArxProcedureType.Function` for a function. Never SQL text. `AddCursorParameter`, `AddVariadicParameter` and `ReturnsCursors` are ARX8001: SQL Server has no cursor parameters and no VARIADIC |
| **Procedure or function** | The caller says which: a procedure runs as `EXEC @arx_return = sp @p_name = @p0, …`, a function as `SELECT * FROM fn(…)` (table-valued) or `SELECT fn(…)` (scalar: ARX finds out on the first call and remembers it). A table-valued function called as a procedure is ARX8001 (SQL Server's error 2809), naming the right `ProcedureType`; a scalar one runs, because SQL Server's `EXEC` accepts it, and its value comes back as the RETURN value. A procedure called as a function is ARX5001 (there is no function by that name) |
| **Arguments** | A procedure's by name (with or without the `@`) or by position (positional first); a function's by position only. Each is a typed parameter through the same value path as Create (dates, JSON, selection sets, lookups). A null whose type matters needs it: `AddParameter(name, type, null)` |
| **What comes back** | `ArxExecuteResult`: `Outputs` (OUTPUT values as their declared types, and the procedure's `RETURN` value as `Outputs[ArxMsSqlDbService.RETURN_VALUE]`), `ResultSets` (each SELECT the procedure runs, by position, or a function's rows as `ResultSets[0]`), `Messages` (`PRINT` and `RAISERROR` of severity 10 or less, in order). `Value<T>()` reads a function's single value |
| **OUT and INOUT** | SQL Server has one kind, `OUTPUT`, which is both: an OUT starts as NULL. ARX sizes each to hold any value of its declared type (`nvarchar(max)`, `decimal(38, 10)`, …), so nothing is cut short |
| **Tables from a procedure** | Every SELECT the procedure runs is a result set, read by position (`ResultSets[0]`, `[1]` …) in one round trip. OUTPUT values arrive after the last one |
| **Paging** | `PageInfo`, as on a query, three ways: the procedure pages itself (`PagingParameters`: ARX fills its page arguments and reads its total), ARX pages a function's rows (`OFFSET … FETCH NEXT`, the total in one round trip), ARX pages a procedure's result set (on the client: earlier rows read and skipped, the rest counted; every row crosses the network). The total is opt-in |
| **Transactions** | ARX's own call runs outside any transaction, so a procedure may begin and commit its own; in an `ArxTransaction` or a batch scope, a procedure that commits or rolls back the caller's transaction is ARX8001 (SQL Server's error 266: the transaction count changed), and the transaction is aborted |
| **Errors the procedure raises** | `THROW` and `RAISERROR` of severity 11 or more are ARX3005 RaisedByDatabase, with the procedure's message and its error number as `NativeCode` (`50101` for `THROW 50101, …`), so the application can branch on its own numbers |
| **Security** | A procedure, function or parameter name carrying SQL is ARX1017, refused before the database; values are parameters |
| **Names in the samples** | Procedures `sp_`, functions `fn_` (views `vw_`) |
| **Without a catalog** | Needs no catalog and reads nothing first: a service created with only the connection string calls procedures and functions as any other |

## Reference

### Error codes an Execute can raise

| Code | When |
|---|---|
| ARX1001 InvalidArgument | A positional argument after a named one; OUT or INOUT parameters on a function; an OUT without a type; `AddOrder` on a procedure; `PageInfo` with nothing to page or below 1; `PagingParameters` without `PageInfo`; `PagedResultSet` naming no result set |
| ARX1002, ARX1003, ARX1004 | No routine name; an invalid name (three parts, a typo); a parameter given twice |
| ARX1007, ARX1013 | A value type ARX can't write; `default(DateTime)` |
| ARX1008 InvalidConnection | A connection with an open transaction, given without that transaction |
| ARX1017 SuspectedInjection | A procedure, function, parameter or paging name carrying SQL |
| ARX2003 MultipleRecordsMatched | `Value<T>()` on several rows |
| ARX3001–3004 | A constraint the procedure broke (kept from the database) |
| ARX3005 RaisedByDatabase | The procedure refused: `THROW`, or `RAISERROR` of severity 11 to 19 (error numbers 50000 and up) |
| ARX4003 InvalidValueFormat | An argument that doesn't convert to the parameter's type (`'abc'` for an `int`) |
| ARX4004 DataTypeMismatch | Arguments of a type the parameter can't take; too many arguments (8144); a named argument the procedure doesn't have (8145); a required parameter left out (201) |
| ARX5001 UndefinedEntity | No procedure or function by that name (2812, 208) |
| ARX5003 SchemaMismatch | An OUT value of another type than declared, that can't be widened |
| ARX6001 PermissionDenied | No `EXECUTE` permission |
| ARX7003 Timeout | `CommandTimeout` exceeded (SQL Server has no statement timeout of its own: the client's applies) |
| ARX8001 NotSupported | A table-valued function called as a procedure (2809); a procedure that commits or rolls back inside an ARX transaction or a scope (266); cursor parameters, VARIADIC, `ReturnsCursors` (before the database); named arguments or result columns on a function |

## The samples

Every Execute sample, in reading order. Each scenario shows the code (verified against SQL Server) and the SQL that checks what it did. `service` is the application's `ArxMsSqlDbService`; values marked "In scope" come from earlier in the sample. What the samples' code refers to beyond the packages (SampleCatalog.cs, `dbContext`) is in the sample application file, arx-sql-sample-application.md.

### Call a stored procedure

Basics 1 of 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.

Usings:

```csharp
using ARX.SDK.Query;
using ARX.SDK.SQL.Services;
using ARX.SDK.Services;
```

Set up by the sample before its scenarios:

```csharp
var service = new ArxMsSqlDbService(connectionString);
var account = new ArxEntity("account");
account["name"] = "Old Name Ltd";
account["account_number"] = "ACC-E101";
var accountId = await service.CreateAsync(account);
```

#### 1. Named arguments

`sp_rename_account(p_account_id, p_name, p_reason)` with every argument named. Nothing comes back.

In scope: `service` (ArxMsSqlDbService), `accountId` (ArxRecordId).

```csharp
var procedure = new ArxProcedureExpression("sp_rename_account");
procedure.AddParameter("p_account_id", accountId.AsInt64());
procedure.AddParameter("p_name", "New Name Ltd");
procedure.AddParameter("p_reason", "rebranding");

var result = await service.ExecuteAsync(procedure);
Console.WriteLine($"renamed; outputs: {result.Outputs.Attributes.Count()}, result sets: {result.ResultSets.Count}");
```

Check it in the database:

```sql
SELECT name
FROM account
WHERE account_number = 'ACC-E101';
```

Expect: New Name Ltd (until scenario 2 renames it again).

#### 2. Positional arguments

The same procedure with positional arguments, in the procedure's order.

In scope: `service` (ArxMsSqlDbService), `accountId` (ArxRecordId).

```csharp
var procedure = new ArxProcedureExpression("sp_rename_account");
procedure.AddParameter(accountId.AsInt64());
procedure.AddParameter("Positional Ltd");
procedure.AddParameter("a positional call");

await service.ExecuteAsync(procedure);
Console.WriteLine("renamed to Positional Ltd");
```

Check it in the database:

```sql
SELECT name
FROM account
WHERE account_number = 'ACC-E101';
```

Expect: Positional Ltd (until scenario 3).

#### 3. A parameter left out: the default is used

`p_reason` left out: the procedure's default (`'not given'`) is used.

In scope: `service` (ArxMsSqlDbService), `accountId` (ArxRecordId).

```csharp
var procedure = new ArxProcedureExpression("sp_rename_account");
procedure.AddParameter("p_account_id", accountId.AsInt64());
procedure.AddParameter("p_name", "Final Name Ltd");

await service.ExecuteAsync(procedure);
Console.WriteLine("renamed to Final Name Ltd, without a reason");
```

Check it in the database:

```sql
SELECT name, CASE WHEN modified_on IS NOT NULL THEN 1 ELSE 0 END AS modified
FROM account
WHERE account_number = 'ACC-E101';
```

Expect: Final Name Ltd, 1.

### Read OUT and INOUT values

Basics 2 of Execute.

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.

Usings:

```csharp
using ARX.SDK.Query;
using ARX.SDK.Services;
```

Set up by the sample before its scenarios:

```csharp
var account = new ArxEntity("account");
account["name"] = "Totals Trading";
account["account_number"] = "ACC-E201";
var accountId = await service.CreateAsync(account);
foreach (var (number, year, total) in new[] { ("SO-E201", 2025, 100m), ("SO-E202", 2026, 250m), ("SO-E203", 2026, 50m) })
{
    var order = new ArxEntity("sales_order");
    order["order_number"] = number;
    order["account_id"] = new ArxEntityReference("account", accountId);
    order["ordered_on"] = new DateTime(year, 6, 1, 12, 0, 0, DateTimeKind.Utc);
    order["total"] = total;
    await service.CreateAsync(order);
}
```

#### 1. A year in, the totals out

`p_year` = 2026 in; the number of orders and the revenue of that year out.

In scope: `service` (ArxMsSqlDbService), `accountId` (ArxRecordId).

```csharp
var outputs = (await service.ExecuteAsync(Totals(accountId, 2026))).Outputs;
Console.WriteLine($"year {outputs["p_year"]}: {outputs["p_orders"]} orders ({outputs["p_orders"]!.GetType().Name}), revenue {outputs["p_revenue"]:N2} ({outputs["p_revenue"]!.GetType().Name})");
```

Defined in the sample, used above:

```csharp
private static ArxProcedureExpression Totals(ArxRecordId accountId, int? year)
{
    var procedure = new ArxProcedureExpression("sp_account_totals");
    procedure.AddParameter("p_account_id", accountId.AsInt64());
    procedure.AddInOutParameter("p_year", year);
    procedure.AddOutParameter<long>("p_orders");
    procedure.AddOutParameter<decimal>("p_revenue");
    return procedure;
}
```

Check it in the database:

```sql
SELECT count(*), sum(total)
FROM sales_order
WHERE order_number LIKE 'SO-E20%' AND YEAR(ordered_on) = 2026;
```

Expect: 2, 300.00.

#### 2. An INOUT the procedure fills in

`p_year` sent as NULL: the procedure fills in the current year and returns it.

In scope: `service` (ArxMsSqlDbService), `accountId` (ArxRecordId).

```csharp
var outputs = (await service.ExecuteAsync(Totals(accountId, null))).Outputs;
Console.WriteLine($"no year given: the procedure used {outputs["p_year"]}, {outputs["p_orders"]} orders");
```

Check it in the database:

```sql
SELECT YEAR(SYSDATETIMEOFFSET()) AS this_year, count(*)
FROM sales_order
WHERE order_number LIKE 'SO-E20%' AND YEAR(ordered_on) = YEAR(SYSDATETIMEOFFSET());
```

Expect: the year and the count the sample printed.

### Call a function: a value, rows, an effect

Basics 3 of Execute.

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).

Usings:

```csharp
using ARX.SDK.Query;
using ARX.SDK.SQL.Services;
using ARX.SDK.Services;
```

Set up by the sample before its scenarios:

```csharp
var account = new ArxEntity("account");
account["name"] = "Function Fabrics";
account["account_number"] = "ACC-E301";
var accountId = await service.CreateAsync(account);
foreach (var (first, title) in new[] { ("Fran", "Buyer"), ("Finn", "Engineer"), ("Faye", "Director") })
{
    var contact = new ArxEntity("contact");
    contact["first_name"] = first;
    contact["last_name"] = "Fn-E301";
    contact["job_title"] = title;
    contact["account_id"] = new ArxEntityReference("account", accountId);
    await service.CreateAsync(contact);
}
```

#### 1. One value

`fn_contact_count(p_account_id)` returns a bigint.

In scope: `service` (ArxMsSqlDbService), `accountId` (ArxRecordId).

```csharp
var function = new ArxProcedureExpression("fn_contact_count") { ProcedureType = ArxProcedureType.Function };
function.AddParameter(accountId.AsInt64());

var count = (await service.ExecuteAsync(function)).Value<long>();
Console.WriteLine($"{count} contacts");
```

Check it in the database:

```sql
SELECT dbo.fn_contact_count(a.id)
FROM account a
WHERE a.account_number = 'ACC-E301';
```

Expect: 3.

#### 2. Rows

`fn_contacts_named(p_last_name)` returns a table: its rows are the first result set.

In scope: `service` (ArxMsSqlDbService).

```csharp
var function = new ArxProcedureExpression("fn_contacts_named") { ProcedureType = ArxProcedureType.Function };
function.AddParameter("Fn-E301");

var rows = (await service.ExecuteAsync(function)).ResultSets[0];
Console.WriteLine(string.Join(", ", rows.Entities.Select(x => $"{x["first_name"]} ({x["job_title"]})")));
```

Check it in the database:

```sql
SELECT first_name, job_title
FROM fn_contacts_named('Fn-E301')
ORDER BY first_name;
```

Expect: 3 rows: Faye Director, Finn Engineer, Fran Buyer.

#### 3. An effect: a procedure, not a function

A SQL Server function can't change data: an effect is a procedure's job. `sp_rename_account` renames the account and sets modified_on; it returns no rows, and its RETURN value is 0.

In scope: `service` (ArxMsSqlDbService), `accountId` (ArxRecordId).

```csharp
var procedure = new ArxProcedureExpression("sp_rename_account");
procedure.AddParameter("p_account_id", accountId.AsInt64());
procedure.AddParameter("p_name", "Fabrikam Functions");

var result = await service.ExecuteAsync(procedure);
Console.WriteLine($"result sets: {result.ResultSets.Count}, RETURN value {result.Outputs[ArxMsSqlDbService.RETURN_VALUE]}");
```

Check it in the database:

```sql
SELECT name, CASE WHEN modified_on IS NOT NULL THEN 1 ELSE 0 END AS touched
FROM account
WHERE account_number = 'ACC-E301';
```

Expect: Fabrikam Functions, 1.

### A procedure that returns tables

Basics 4 of Execute.

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.

Usings:

```csharp
using ARX.SDK.Query;
using ARX.SDK.Services;
```

Set up by the sample before its scenarios:

```csharp
var account = new ArxEntity("account");
account["name"] = "Tables Inc";
account["account_number"] = "ACC-E401";
var accountId = await service.CreateAsync(account);
foreach (var (sku, price) in new[] { ("SKU-E401", 10m), ("SKU-E402", 4.5m) })
{
    var product = new ArxEntity("product");
    product["sku"] = sku;
    product["name"] = $"Product {sku}";
    product["unit_price"] = price;
    await service.CreateAsync(product);
}
var place = new ArxProcedureExpression("sp_place_order");
place.AddParameter("p_account_id", accountId.AsInt64());
place.AddParameter("p_order_number", "SO-E401");
place.AddParameter("p_skus", "SKU-E401,SKU-E402");
place.AddParameter("p_quantity", 2);
place.AddOutParameter<long>("p_order_id");
place.AddOutParameter<decimal>("p_total");
var orderId = (long)(await service.ExecuteAsync(place)).Outputs["p_order_id"]!;
```

#### 1. One table

`sp_order_with_lines`'s second table, the lines: `ResultSets[1]`.

In scope: `service` (ArxMsSqlDbService), `orderId` (long).

```csharp
var procedure = new ArxProcedureExpression("sp_order_with_lines");
procedure.AddParameter("p_order_id", orderId);

var lines = (await service.ExecuteAsync(procedure)).ResultSets[1];
Console.WriteLine(string.Join(", ", lines.Entities.Select(x => $"{x["sku"]} x{x["quantity"]} at {x["unit_price"]:N2}")));
```

Check it in the database:

```sql
SELECT p.sku, l.quantity, l.unit_price
FROM order_line l
JOIN sales_order o ON o.id = l.order_id
JOIN product p ON p.id = l.product_id
WHERE o.order_number = 'SO-E401'
ORDER BY p.sku;
```

Expect: 2 rows: SKU-E401 2 10.00, SKU-E402 2 4.50.

#### 2. Two tables, by position

Both tables: the order (`ResultSets[0]`) and its lines (`ResultSets[1]`).

In scope: `service` (ArxMsSqlDbService), `orderId` (long).

```csharp
var procedure = new ArxProcedureExpression("sp_order_with_lines");
procedure.AddParameter("p_order_id", orderId);

var result = await service.ExecuteAsync(procedure);
var order = result.ResultSets[0].Entities.Single();
Console.WriteLine($"{order["order_number"]} for {order["account"]}, total {order["total"]:N2}; {result.ResultSets[1].Entities.Count} lines (ResultSets[1] is \"{result.ResultSets[1].EntityName}\")");
```

Check it in the database:

```sql
SELECT o.order_number, a.name, o.total, (SELECT count(*) FROM order_line l WHERE l.order_id = o.id) AS lines
FROM sales_order o
JOIN account a ON a.id = o.account_id
WHERE o.order_number = 'SO-E401';
```

Expect: SO-E401, Tables Inc, 29.00, 2.

### Place an order through a procedure

Real case 1 of Execute.

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.

Usings:

```csharp
using ARX.SDK.Query;
using ARX.SDK.Services;
```

Set up by the sample before its scenarios:

```csharp
var account = new ArxEntity("account");
account["name"] = "Order Oasis";
account["account_number"] = "ACC-E501";
var accountId = await service.CreateAsync(account);
foreach (var (sku, price) in new[] { ("SKU-E501", 12m), ("SKU-E502", 3.25m), ("SKU-E503", 99m) })
{
    var product = new ArxEntity("product");
    product["sku"] = sku;
    product["name"] = $"Product {sku}";
    product["unit_price"] = price;
    await service.CreateAsync(product);
}
```

#### 1. Place the order

Two products, three of each: the procedure prices them and returns the order's id and total.

In scope: `service` (ArxMsSqlDbService), `accountId` (ArxRecordId).

```csharp
var place = new ArxProcedureExpression("sp_place_order");
place.AddParameter("p_account_id", accountId.AsInt64());
place.AddParameter("p_order_number", "SO-E501");
place.AddParameter("p_skus", "SKU-E501,SKU-E502");
place.AddParameter("p_quantity", 3);
place.AddOutParameter<long>("p_order_id");
place.AddOutParameter<decimal>("p_total");

var outputs = (await service.ExecuteAsync(place)).Outputs;
Console.WriteLine($"order {outputs["p_order_id"]} placed, total {outputs["p_total"]:N2}");

return (long)outputs["p_order_id"]!;
```

Check it in the database:

```sql
SELECT o.total, count(l.id) AS lines, sum(l.quantity) AS units
FROM sales_order o
JOIN order_line l ON l.order_id = o.id
WHERE o.order_number = 'SO-E501'
GROUP BY o.total;
```

Expect: 45.75, 2, 6.

#### 2. Read it back by the returned id

The returned id is the order's id: Retrieve reads the order by it.

In scope: `service` (ArxMsSqlDbService), `orderId` (long).

```csharp
var order = await service.RetrieveAsync("sales_order", orderId, new ArxColumnSet("order_number", "total"));
Console.WriteLine($"{order!["order_number"]}: {order["total"]:N2}");
```

Check it in the database:

```sql
SELECT order_number, total
FROM sales_order
WHERE order_number = 'SO-E501';
```

Expect: SO-E501, 45.75.

### A report with several result sets

Real case 2 of Execute.

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.

Usings:

```csharp
using ARX.SDK.Query;
using ARX.SDK.Services;
```

Set up by the sample before its scenarios:

```csharp
var account = new ArxEntity("account");
account["name"] = "Report Rangers";
account["account_number"] = "ACC-E601";
var accountId = await service.CreateAsync(account);
foreach (var first in new[] { "Rae", "Rob" })
{
    var contact = new ArxEntity("contact");
    contact["first_name"] = first;
    contact["last_name"] = "Report-E601";
    contact["account_id"] = new ArxEntityReference("account", accountId);
    await service.CreateAsync(contact);
}
foreach (var (number, total) in new[] { ("SO-E601", 120m), ("SO-E602", 80m) })
{
    var order = new ArxEntity("sales_order");
    order["order_number"] = number;
    order["account_id"] = new ArxEntityReference("account", accountId);
    order["ordered_on"] = new DateTime(2026, 11, 8, 9, 0, 0, DateTimeKind.Utc);
    order["total"] = total;
    await service.CreateAsync(order);
}
```

#### 1. Three tables in one call

Contacts, orders and a summary: ResultSets[0], [1] and [2].

In scope: `service` (ArxMsSqlDbService), `accountId` (ArxRecordId).

```csharp
var report = new ArxProcedureExpression("sp_account_report");
report.AddParameter("p_account_id", accountId.AsInt64());

var sets = (await service.ExecuteAsync(report)).ResultSets;
var summary = sets[2].Entities.Single();

Console.WriteLine($"contacts: {string.Join(", ", sets[0].Entities.Select(x => x["first_name"]))}");
Console.WriteLine($"orders: {string.Join(", ", sets[1].Entities.Select(x => $"{x["order_number"]} {x["total"]:N2}"))}");
Console.WriteLine($"summary: {summary["contacts"]} contacts, {summary["orders"]} orders, revenue {summary["revenue"]:N2}");
```

Check it in the database:

```sql
SELECT (SELECT count(*) FROM contact c WHERE c.account_id = a.id) AS contacts,
       (SELECT count(*) FROM sales_order o WHERE o.account_id = a.id) AS orders,
       (SELECT sum(total) FROM sales_order o WHERE o.account_id = a.id) AS revenue
FROM account a
WHERE a.account_number = 'ACC-E601';
```

Expect: 2, 2, 200.00.

### Page through a procedure's result: the procedure pages itself, ARX pages a function, ARX pages a result set

Real case 3 of Execute.

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.

- **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.
- **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.
- **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.
- Which to choose.

Usings:

```csharp
using ARX.SDK.Query;
using ARX.SDK.Services;
```

Set up by the sample before its scenarios:

```csharp
var products = Enumerable.Range(1, 120).Select(i =>
{
    var product = new ArxEntity("product");
    product["sku"] = $"{PREFIX}{i:D3}";
    product["name"] = $"Paged product {i}";
    product["unit_price"] = 1m + i % 10;
    return product;
}).ToList();
await service.CreateMultipleAsync(products);
```

#### 1. The procedure pages itself

`sp_products_page(p_prefix, p_page, p_page_size, INOUT p_rows, INOUT p_total)` pages itself. ARX fills `p_page` and `p_page_size`, reads the table from `p_rows` and the total from `p_total`.

In scope: `service` (ArxMsSqlDbService).

```csharp
foreach (var pageNumber in new[] { 1, 3 })
{
    var procedure = new ArxProcedureExpression("sp_products_page");
    procedure.AddParameter("p_prefix", PREFIX);
    procedure.PageInfo = new ArxPagingInfo { Count = 50, PageNumber = pageNumber, ReturnTotalRecordCount = pageNumber == 1 };
    procedure.PagingParameters = ArxPagingParameters.PageNumber("p_page", "p_page_size", totalRecordCount: "p_total");

    Console.WriteLine(Describe((await service.ExecuteAsync(procedure)).ResultSets[0]));
}
```

Defined in the sample, used above:

```csharp
private static string Describe(ArxEntityCollection page)
    => $"page {page.CurrentPage}: {page.Entities.Count} rows ({page.Entities.FirstOrDefault()?["sku"]} to {page.Entities.LastOrDefault()?["sku"]}), next page: {page.HasNextPage}"
       + (page.TotalRecordCount > 0 ? $", {page.TotalRecordCount} in all, {page.TotalPageCount} pages" : string.Empty);
```

Check it in the database:

```sql
SELECT count(*), min(sku), max(sku)
FROM (SELECT sku FROM product WHERE sku LIKE 'PG-E7%' ORDER BY sku OFFSET 100 ROWS FETCH NEXT 50 ROWS ONLY) AS page_3;
```

Expect: 20, PG-E7101, PG-E7120 (page 1 is PG-E7001 to PG-E7050; 120 in all, 3 pages).

#### 2. ARX pages a function's rows

`fn_products_like(p_prefix)` returns every matching product; ARX orders and pages its rows, with the total in the same round trip.

In scope: `service` (ArxMsSqlDbService).

```csharp
var function = new ArxProcedureExpression("fn_products_like") { ProcedureType = ArxProcedureType.Function };
function.AddParameter(PREFIX);
function.AddOrder("unit_price", ArxOrderType.Descending);
function.AddOrder("sku", ArxOrderType.Ascending);
function.PageInfo = new ArxPagingInfo { Count = 25, PageNumber = 2, ReturnTotalRecordCount = true };

var page = (await service.ExecuteAsync(function)).ResultSets[0];
Console.WriteLine(Describe(page) + $"; prices {page.Entities[0]["unit_price"]:N2} to {page.Entities[^1]["unit_price"]:N2}");
```

Check it in the database:

```sql
SELECT sku, unit_price
FROM fn_products_like('PG-E7')
ORDER BY unit_price DESC, sku OFFSET 25 ROWS FETCH NEXT 25 ROWS ONLY;
```

Expect: 25 rows, PG-E7017 (8.00) first and PG-E7015 (6.00) last (120 in all, 5 pages).

#### 3. ARX pages a table the procedure returns

`sp_products` returns every matching product as a result set; ARX pages it.

In scope: `service` (ArxMsSqlDbService).

```csharp
var procedure = new ArxProcedureExpression("sp_products");
procedure.AddParameter("p_prefix", PREFIX);
procedure.PageInfo = new ArxPagingInfo { Count = 40, PageNumber = 3, ReturnTotalRecordCount = true };

Console.WriteLine(Describe((await service.ExecuteAsync(procedure)).ResultSets[0]));
```

Check it in the database:

```sql
SELECT count(*), min(sku), max(sku)
FROM (SELECT sku FROM product WHERE sku LIKE 'PG-E7%' ORDER BY sku OFFSET 80 ROWS FETCH NEXT 40 ROWS ONLY) AS page_3;
```

Expect: 40, PG-E7081, PG-E7120: the last page (no next page; 120 in all, 3 pages).

#### 4. Which to choose

The choice, with the numbers of `ExecutePagingBenchmarks`.

```csharp
Console.WriteLine("large data, deep pages: let the routine page itself (1); the database produces only the page.");
Console.WriteLine("an inline table-valued function: let ARX page it (2); SQL Server expands it, so the limit reaches the index.");
Console.WriteLine("a multi-statement function or a procedure's result set: ARX pages it (2, 3), but every skipped row is still produced (and, for 3, sent).");
Console.WriteLine("the total costs a count of every row: ask for it on page 1 only, or not at all for \"load more\".");
```

### Business errors raised by a procedure

Real case 4 of Execute.

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.

Usings:

```csharp
using ARX.SDK;
using ARX.SDK.Query;
using ARX.SDK.Services;
```

Set up by the sample before its scenarios:

```csharp
var open = new ArxEntity("account");
open["name"] = "Credit Cove";
open["account_number"] = "ACC-E801";
open["credit_limit"] = 100m;
var openId = await service.CreateAsync(open);
var held = new ArxEntity("account");
held["name"] = "Held Harbour";
held["account_number"] = "ACC-E802";
held["credit_limit"] = 500m;
held["status"] = new ArxSelectionSetValue("AccountStatus", "ON_HOLD");
var heldId = await service.CreateAsync(held);
```

#### 1. Within the limit

40 of a limit of 100: reserved.

In scope: `service` (ArxMsSqlDbService), `accountId` (ArxRecordId).

```csharp
await service.ExecuteAsync(Reserve(accountId, 40m));
Console.WriteLine("40.00 reserved");
```

Defined in the sample, used above:

```csharp
private static ArxProcedureExpression Reserve(ArxRecordId accountId, decimal amount)
{
    var procedure = new ArxProcedureExpression("sp_reserve_credit");
    procedure.AddParameter("p_account_id", accountId.AsInt64());
    procedure.AddParameter("p_amount", amount);
    return procedure;
}
```

Check it in the database:

```sql
SELECT credit_limit
FROM account
WHERE account_number = 'ACC-E801';
```

Expect: 60.00.

#### 2. Over the limit

250 more: refused, with the procedure's message and number; nothing changed.

In scope: `service` (ArxMsSqlDbService), `accountId` (ArxRecordId).

```csharp
try
{
    await service.ExecuteAsync(Reserve(accountId, 250m));
}
catch (ArxDatabaseException error) when (error.Code == ArxErrorCode.RaisedByDatabase)
{
    Console.WriteLine(error.Message);
    Console.WriteLine($"number {error.NativeCode}; for the user: \"{ForTheUser(error)}\"");
}
```

Defined in the sample, used above:

```csharp
private static ArxProcedureExpression Reserve(ArxRecordId accountId, decimal amount)
{
    var procedure = new ArxProcedureExpression("sp_reserve_credit");
    procedure.AddParameter("p_account_id", accountId.AsInt64());
    procedure.AddParameter("p_amount", amount);
    return procedure;
}

private static string ForTheUser(ArxException error) => error.NativeCode switch
{
    "50101" => "This account is on hold. Please contact accounts.",
    "50102" => "That's over the credit limit.",
    _ => "The request couldn't be completed."
};
```

Check it in the database:

```sql
SELECT credit_limit
FROM account
WHERE account_number = 'ACC-E801';
```

Expect: 60.00: the refused call changed nothing.

#### 3. An account on hold: the procedure's own number

An account on hold: the procedure's own number, `50101`, so the application shows another message.

In scope: `service` (ArxMsSqlDbService), `accountId` (ArxRecordId).

```csharp
try
{
    await service.ExecuteAsync(Reserve(accountId, 10m));
}
catch (ArxDatabaseException error) when (error.Code == ArxErrorCode.RaisedByDatabase)
{
    Console.WriteLine($"{error.NativeCode}: {error.Message}; for the user: \"{ForTheUser(error)}\"");
}
```

Check it in the database:

```sql
SELECT status, credit_limit
FROM account
WHERE account_number = 'ACC-E802';
```

Expect: ON_HOLD, 500.00.

### A nightly job that commits in batches

Real case 5 of Execute.

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.

Usings:

```csharp
using ARX.SDK;
using ARX.SDK.Query;
using ARX.SDK.Services;
```

Set up by the sample before its scenarios:

```csharp
var account = new ArxEntity("account");
account["name"] = "Nightly Nets";
account["account_number"] = "ACC-E901";
var accountId = await service.CreateAsync(account);
var product = new ArxEntity("product");
product["sku"] = "SKU-E901";
product["name"] = "Recalculated product";
product["unit_price"] = 5m;
var productId = await service.CreateAsync(product);
for (var i = 1; i <= 7; i++)
{
    var order = new ArxEntity("sales_order");
    order["order_number"] = $"SO-E9{i:D2}";
    order["account_id"] = new ArxEntityReference("account", accountId);
    order["ordered_on"] = new DateTime(2026, 11, 8, 1, 0, 0, DateTimeKind.Utc);
    order["total"] = -1m;
    var orderId = await service.CreateAsync(order);

    var line = new ArxEntity("order_line");
    line["order_id"] = new ArxEntityReference("sales_order", orderId);
    line["product_id"] = new ArxEntityReference("product", productId);
    line["quantity"] = 2;
    line["unit_price"] = 5m;
    await service.CreateAsync(line);
}
```

#### 1. Not inside an ARX transaction

The job in an ARX transaction: SQL Server reports the changed transaction count (266), ARX8001 says why, and the transaction rolls back.

In scope: `service` (ArxMsSqlDbService), `connectionString` (string).

```csharp
await using var connection = new ARX.SDK.SQL.Services.ArxMsSqlConnection(connectionString);
await using var transaction = await connection.BeginTransactionAsync();

try
{
    await service.ExecuteAsync(Job(), transaction);
}
catch (ArxDatabaseException error) when (error.Code == ArxErrorCode.NotSupported)
{
    Console.WriteLine(error.Message);
}
```

Defined in the sample, used above:

```csharp
private static ArxProcedureExpression Job()
{
    var procedure = new ArxProcedureExpression("sp_recalculate_totals");
    procedure.AddParameter("p_prefix", "SO-E9");
    procedure.AddParameter("p_batch", 3);
    return procedure;
}
```

Check it in the database:

```sql
SELECT count(*)
FROM sales_order
WHERE order_number LIKE 'SO-E9%' AND total < 0;
```

Expect: 7 right after this scenario (the refused call changed nothing); 0 once scenario 2 has run.

#### 2. Run the job alone: a commit per batch

Batches of three, each committed; the progress arrives as messages. A long job sets a longer timeout.

In scope: `service` (ArxMsSqlDbService).

```csharp
var result = await service.ExecuteAsync(Job(), new ArxExecuteOptions { CommandTimeout = 600 });

foreach (var message in result.Messages)
{
    Console.WriteLine($"{message.Severity}: {message.Text}");
}
```

Check it in the database:

```sql
SELECT SUM(CASE WHEN total = 10 THEN 1 ELSE 0 END) AS recalculated, SUM(CASE WHEN total < 0 THEN 1 ELSE 0 END) AS [left]
FROM sales_order
WHERE order_number LIKE 'SO-E9%';
```

Expect: 7, 0.

### Progress messages from a procedure

Real case 6 of Execute.

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.

Usings:

```csharp
using ARX.SDK.Query;
using ARX.SDK.Services;
```

#### 1. The messages, in order

An import of 1,000 rows: a progress message every 250 rows, then one because it's large.

In scope: `service` (ArxMsSqlDbService).

```csharp
var result = await service.ExecuteAsync(Import(1_000));

foreach (var message in result.Messages)
{
    Console.WriteLine($"{message.Severity,-7} {message.Text}");
}
```

Defined in the sample, used above:

```csharp
private static ArxProcedureExpression Import(int rows)
{
    var procedure = new ArxProcedureExpression("sp_import_progress");
    procedure.AddParameter("p_rows", rows);
    return procedure;
}
```

Check it in the database:

```sql
SELECT name, type_desc
FROM sys.objects
WHERE name = 'sp_import_progress';
```

Expect: sp_import_progress, SQL_STORED_PROCEDURE (the messages themselves are only seen by the session that ran the call).

#### 2. Not collected

The same call without collecting them.

In scope: `service` (ArxMsSqlDbService).

```csharp
var result = await service.ExecuteAsync(Import(1_000), new ArxExecuteOptions { CollectMessages = false });
Console.WriteLine($"{result.Messages.Count} messages");
```

Check it in the database:

```sql
SELECT COUNT(*)
FROM sys.procedures
WHERE name = 'sp_import_progress';
```

Expect: 1.

### Handle Execute errors

Real case 7 of Execute.

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).

Usings:

```csharp
using ARX.SDK;
using ARX.SDK.Query;
using ARX.SDK.Services;
```

#### 1. What the database refuses

Mapped from SQL Server's errors.

In scope: `service` (ArxMsSqlDbService).

```csharp
await TryAsync("a procedure that doesn't exist", () => service.ExecuteAsync(new ArxProcedureExpression("sp_no_such_procedure")));

var wrongTypes = new ArxProcedureExpression("sp_rename_account");
wrongTypes.AddParameter("p_account_id", Guid.NewGuid());
wrongTypes.AddParameter("p_name", "x");
await TryAsync("the wrong argument types", () => service.ExecuteAsync(wrongTypes));

// A table-valued function called as a procedure. (A scalar one would run: SQL Server's EXEC accepts it and returns
// its value as the RETURN value. Call functions as functions either way.)
var function = new ArxProcedureExpression("fn_contacts_named");
function.AddParameter("Report-E601");
await TryAsync("a function called as a procedure", () => service.ExecuteAsync(function));

var slow = new ArxProcedureExpression("sp_slow");
slow.AddParameter("p_seconds", 3.0);
await TryAsync("a timeout of 1 second", () => service.ExecuteAsync(slow, new ArxExecuteOptions { CommandTimeout = 1 }));
```

Defined in the sample, used above:

```csharp
private static async Task TryAsync(string label, Func<Task> call)
{
    try
    {
        await call();
        Console.WriteLine($"{label}: no error");
    }
    catch (ArxException error)
    {
        Console.WriteLine($"{label}: {error.Code} ({(int)error.Code}), {error.Category}");
    }
}
```

Check it in the database:

```sql
SELECT name, type
FROM sys.objects
WHERE name IN ('sp_no_such_procedure', 'sp_rename_account', 'fn_contact_count', 'sp_slow')
ORDER BY name;
```

Expect: 3 rows: fn_contact_count FN, sp_rename_account P, sp_slow P (no sp_no_such_procedure).

#### 2. What ARX refuses first

Refused before any connection opens.

In scope: `service` (ArxMsSqlDbService).

```csharp
var mixed = new ArxProcedureExpression("sp_rename_account");
mixed.AddParameter("p_account_id", 1L);
mixed.AddParameter("positional after a named one");
await TryAsync("a positional argument after a named one", () => service.ExecuteAsync(mixed));

var untyped = new ArxProcedureExpression("sp_account_totals");
untyped.Parameters.Add(new ArxProcedureParameter("p_orders", null, null, ArxProcedureParameterDirection.Out));
await TryAsync("an OUT parameter without a type", () => service.ExecuteAsync(untyped));

var cursor = new ArxProcedureExpression("sp_order_with_lines");
cursor.AddCursorParameter("p_lines");
await TryAsync("a cursor parameter (SQL Server has none)", () => service.ExecuteAsync(cursor));
```

Check it in the database:

```sql
SELECT count(*)
FROM account
WHERE id = 1 AND name = 'positional after a named one';
```

Expect: 0: nothing reached the database.

### Catch an SQL injection attempt on Execute

Real case 8 of Execute.

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.

Usings:

```csharp
using ARX.SDK;
using ARX.SDK.Query;
using ARX.SDK.Services;
```

Set up by the sample before its scenarios:

```csharp
var account = new ArxEntity("account");
account["name"] = "Injection: keep me";
account["account_number"] = "ACC-I801";
var accountId = await service.CreateAsync(account);
```

#### 1. A routine name from the request carries SQL

`POST /api/run/{report}` with the report used as the procedure name.

In scope: `service` (ArxMsSqlDbService).

```csharp
try
{
    await service.ExecuteAsync(new ArxProcedureExpression("sp_account_report(1); DROP TABLE account; --"));
}
catch (ArxValidationException error) when (error.Code == ArxErrorCode.SuspectedInjection)
{
    SecurityLog(error);
}
```

Defined in the sample, used above:

```csharp
private static void SecurityLog(ArxValidationException error) => Console.WriteLine($"SECURITY: {error.Message}");
```

Check it in the database:

```sql
SELECT count(*)
FROM account
WHERE account_number = 'ACC-I801';
```

Expect: 1: the table is there.

#### 2. A parameter name from the request carries SQL

A form whose field names become parameter names.

In scope: `service` (ArxMsSqlDbService), `accountId` (ArxRecordId).

```csharp
var procedure = new ArxProcedureExpression("sp_rename_account");
procedure.AddParameter("p_account_id", accountId.AsInt64());
procedure.AddParameter("p_name => 'x'); DELETE FROM account; --", "Pwned");

try
{
    await service.ExecuteAsync(procedure);
}
catch (ArxValidationException error) when (error.Code == ArxErrorCode.SuspectedInjection)
{
    SecurityLog(error);
}
```

Check it in the database:

```sql
SELECT name
FROM account
WHERE account_number = 'ACC-I801';
```

Expect: Injection: keep me (until scenario 3).

#### 3. SQL in a value is a value

SQL typed into a value: it's the account's new name, nothing more.

In scope: `service` (ArxMsSqlDbService), `accountId` (ArxRecordId).

```csharp
var procedure = new ArxProcedureExpression("sp_rename_account");
procedure.AddParameter("p_account_id", accountId.AsInt64());
procedure.AddParameter("p_name", "x'); DROP TABLE account; --");

await service.ExecuteAsync(procedure);
Console.WriteLine("renamed, literally");
```

Check it in the database:

```sql
SELECT name
FROM account
WHERE account_number = 'ACC-I801';
```

Expect: x'); DROP TABLE account; --

### Execute in an ARX transaction

Advanced case 1 of Execute.

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.

Usings:

```csharp
using ARX.SDK;
using ARX.SDK.Query;
using ARX.SDK.SQL.Services;
using ARX.SDK.Services;
```

Set up by the sample before its scenarios:

```csharp
var product = new ArxEntity("product");
product["sku"] = "SKU-EA11";
product["name"] = "Transactional product";
product["unit_price"] = 20m;
await service.CreateAsync(product);
```

#### 1. Create, call, read inside, roll back

Create an account, place its order through the procedure, read the order inside the transaction, roll back.

In scope: `service` (ArxMsSqlDbService), `connectionString` (string).

```csharp
await using var connection = new ArxMsSqlConnection(connectionString);
await using var transaction = await connection.BeginTransactionAsync();

var account = new ArxEntity("account");
account["name"] = "Rolled Back Retail";
account["account_number"] = "ACC-EA11";
var accountId = await service.CreateAsync(account, transaction);

var place = new ArxProcedureExpression("sp_place_order");
place.AddParameter("p_account_id", accountId.AsInt64());
place.AddParameter("p_order_number", "SO-EA11");
place.AddParameter("p_skus", "SKU-EA11");
place.AddParameter("p_quantity", 4);
place.AddOutParameter<long>("p_order_id");
place.AddOutParameter<decimal>("p_total");
var outputs = (await service.ExecuteAsync(place, transaction)).Outputs;

var inside = await service.RetrieveAsync("sales_order", (long)outputs["p_order_id"]!, transaction, new ArxColumnSet("total"));
Console.WriteLine($"inside the transaction: order total {inside!["total"]:N2}");

await transaction.RollbackAsync();
Console.WriteLine("rolled back");
```

Check it in the database:

```sql
SELECT (SELECT count(*) FROM account WHERE account_number = 'ACC-EA11') AS accounts,
       (SELECT count(*) FROM sales_order WHERE order_number = 'SO-EA11') AS orders;
```

Expect: 0, 0: the account and the procedure's order went with the rollback.

#### 2. A raised error aborts the transaction

The procedure refuses (ARX3005); the transaction is aborted, so the next call is ARX1015.

In scope: `service` (ArxMsSqlDbService), `connectionString` (string).

```csharp
await using var connection = new ArxMsSqlConnection(connectionString);
await using var transaction = await connection.BeginTransactionAsync();

var account = new ArxEntity("account");
account["name"] = "Aborted Atelier";
account["account_number"] = "ACC-EA12";
account["credit_limit"] = 10m;
var accountId = await service.CreateAsync(account, transaction);

var reserve = new ArxProcedureExpression("sp_reserve_credit");
reserve.AddParameter("p_account_id", accountId.AsInt64());
reserve.AddParameter("p_amount", 50m);

try
{
    await service.ExecuteAsync(reserve, transaction);
}
catch (ArxDatabaseException error) when (error.Code == ArxErrorCode.RaisedByDatabase)
{
    Console.WriteLine($"refused: {error.Message}; aborted: {transaction.IsAborted}");
}

try
{
    await service.RetrieveAsync("account", accountId, transaction);
}
catch (ArxValidationException error) when (error.Code == ArxErrorCode.TransactionAborted)
{
    Console.WriteLine(error.Message);
}
```

Check it in the database:

```sql
SELECT count(*)
FROM account
WHERE account_number = 'ACC-EA12';
```

Expect: 0: the aborted transaction was rolled back when it was disposed.

### Execute in a batch scope: a refused call undone alone

Advanced case 2 of Execute.

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.

Usings:

```csharp
using ARX.SDK;
using ARX.SDK.Query;
using ARX.SDK.Services;
```

Set up by the sample before its scenarios:

```csharp
var product = new ArxEntity("product");
product["sku"] = "SKU-EA21";
product["name"] = "Scoped product";
product["unit_price"] = 10m;
await service.CreateAsync(product);
var account = new ArxEntity("account");
account["name"] = "Scoped Supplies";
account["account_number"] = "ACC-EA21";
account["credit_limit"] = 100m;
var accountId = await service.CreateAsync(account);
```

#### 1. Import three orders in one scope

Three orders: each is a credit reservation then the order. The second is over the limit: its reservation is refused and the order skipped; the scope stays active.

In scope: `service` (ArxMsSqlDbService), `accountId` (ArxRecordId).

```csharp
await service.RunBatchAsync(async scope =>
{
    foreach (var (number, amount, quantity) in new[] { ("SO-EA21", 30m, 3), ("SO-EA22", 500m, 50), ("SO-EA23", 20m, 2) })
    {
        var reserve = new ArxProcedureExpression("sp_reserve_credit");
        reserve.AddParameter("p_account_id", accountId.AsInt64());
        reserve.AddParameter("p_amount", amount);

        try
        {
            await scope.ExecuteAsync(reserve);
        }
        catch (ArxDatabaseException error) when (error.Code == ArxErrorCode.RaisedByDatabase)
        {
            Console.WriteLine($"{number}: refused in call {error.BatchNumber} ({error.Message}); the scope is {scope.State}");
            continue;
        }

        var place = new ArxProcedureExpression("sp_place_order");
        place.AddParameter("p_account_id", accountId.AsInt64());
        place.AddParameter("p_order_number", number);
        place.AddParameter("p_skus", "SKU-EA21");
        place.AddParameter("p_quantity", quantity);
        place.AddOutParameter<long>("p_order_id");
        place.AddOutParameter<decimal>("p_total");
        var total = (await scope.ExecuteAsync(place)).Outputs["p_total"];
        Console.WriteLine($"{number}: placed, total {total:N2}");
    }
}, new ArxBatchOptions { Savepoints = true });
```

Check it in the database:

```sql
SELECT (SELECT STRING_AGG(CONCAT(order_number, ' ', total), ', ') WITHIN GROUP (ORDER BY order_number) FROM sales_order WHERE order_number LIKE 'SO-EA2%') AS orders,
       (SELECT credit_limit FROM account WHERE account_number = 'ACC-EA21') AS credit_left;
```

Expect: SO-EA21 30.00, SO-EA23 20.00 | 50.00.

### Export what a procedure returns, page by page

Advanced case 3 of Execute.

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.

Usings:

```csharp
using ARX.SDK.Query;
using ARX.SDK.Services;
```

Set up by the sample before its scenarios:

```csharp
var products = Enumerable.Range(1, 1_050).Select(i =>
{
    var product = new ArxEntity("product");
    product["sku"] = $"{PREFIX}-{i:D4}";
    product["name"] = $"Exported product {i}";
    product["unit_price"] = 1m + i % 7;
    return product;
}).ToList();
await service.CreateMultipleAsync(products);
```

#### 1. Every page, the total asked once

Pages of 200: the first says how many pages there are; the loop ends on HasNextPage.

In scope: `service` (ArxMsSqlDbService).

```csharp
var exported = 0;
var sum = 0m;
var pageCount = 0;
ArxEntityCollection page;
var number = 0;

do
{
    var procedure = new ArxProcedureExpression("sp_products_page");
    procedure.AddParameter("p_prefix", PREFIX);
    procedure.PageInfo = new ArxPagingInfo { Count = 200, PageNumber = ++number, ReturnTotalRecordCount = number == 1 };
    procedure.PagingParameters = ArxPagingParameters.PageNumber("p_page", "p_page_size", totalRecordCount: "p_total");

    page = (await service.ExecuteAsync(procedure)).ResultSets[0];

    if (number == 1)
    {
        pageCount = page.TotalPageCount;
    }

    exported += page.Entities.Count;
    sum += page.Entities.Sum(x => (decimal)x["unit_price"]!);
}
while (page.HasNextPage);

Console.WriteLine($"{exported} products in {number} pages (page 1 said {pageCount}), prices summing to {sum:N2}");
```

Check it in the database:

```sql
SELECT count(*), sum(unit_price), CEILING(count(*) / 200.0)
FROM product
WHERE sku LIKE 'EXP-EA3%';
```

Expect: 1050, the sum the sample printed, 6.

### A security check around Execute: probing, blocking, unblocking a false positive

Advanced case 4 of Execute.

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.

Usings:

```csharp
using ARX.SDK.Query;
using ARX.SDK.Services;
```

Set up by the sample before its scenarios:

```csharp
var guard = new SecurityGuard(blockAfter: 3);
var account = new ArxEntity("account");
account["name"] = "Guarded Goods";
account["account_number"] = "ACC-EA41";
var accountId = await service.CreateAsync(account);
```

#### 1. A report through the allowed list

`POST /api/reports/account-report`: run.

In scope: `service` (ArxMsSqlDbService), `guard` (SecurityGuard), `accountId` (ArxRecordId).

```csharp
var result = await RunThroughTheListAsync(service, guard, "ana", "account-report", accountId);
var unknown = await RunThroughTheListAsync(service, guard, "ana", "sp_account_report; DROP TABLE account", accountId);

Console.WriteLine($"ana: {result.Outcome}, {result.Value!.ResultSets[2].Entities[0]["contacts"]} contacts; an unknown report: \"{unknown.ForTheUser}\" (never reaches ARX)");
```

Defined in the sample, used above:

```csharp
private static readonly Dictionary<string, string> Reports = new(StringComparer.OrdinalIgnoreCase)
{
    ["account-report"] = "sp_account_report",
};

private static ArxProcedureExpression Report(string procedureName, ArxRecordId accountId)
{
    var report = new ArxProcedureExpression(procedureName);
    report.AddParameter("p_account_id", accountId.AsInt64());
    return report;
}

private static Task<GuardResult<ArxExecuteResult>> RunThroughTheListAsync(ArxMsSqlDbService service, SecurityGuard guard, string user, string report, ArxRecordId accountId)
    => Reports.TryGetValue(report, out var procedure)
        ? guard.RunAsync(user, $"report {report}", NameSource.Code, () => service.ExecuteAsync(Report(procedure, accountId)))
        : Task.FromResult(new GuardResult<ArxExecuteResult>(GuardOutcome.Refused, null, $"404 Not Found: no report '{report}'."));
```

Check it in the database:

```sql
SELECT count(*)
FROM contact c
JOIN account a ON a.id = c.account_id
WHERE a.account_number = 'ACC-EA41';
```

Expect: 0, the contacts the report counted.

#### 2. Probing the legacy endpoint gets the user blocked

The legacy endpoint is probed three times: each probe is refused by ARX (ARX1017) and logged; then the user is blocked.

In scope: `service` (ArxMsSqlDbService), `guard` (SecurityGuard), `accountId` (ArxRecordId).

```csharp
foreach (var probe in new[] { "sp_account_report; DROP TABLE account; --", "sp_account_report\"(1); --", "sp_account_report WAITFOR DELAY '0:0:5'" })
{
    await RunDirectlyAsync(service, guard, "mallory", probe, accountId);
}

var valid = await RunDirectlyAsync(service, guard, "mallory", "sp_account_report", accountId);

Console.WriteLine($"mallory: {guard.Events.Count(x => x.User == "mallory")} events logged, blocked: {guard.IsBlocked("mallory")}; a valid report: {valid.Outcome}");
```

Defined in the sample, used above:

```csharp
private static ArxProcedureExpression Report(string procedureName, ArxRecordId accountId)
{
    var report = new ArxProcedureExpression(procedureName);
    report.AddParameter("p_account_id", accountId.AsInt64());
    return report;
}

private static Task<GuardResult<ArxExecuteResult>> RunDirectlyAsync(ArxMsSqlDbService service, SecurityGuard guard, string user, string procedure, ArxRecordId accountId)
    => guard.RunAsync(user, $"report {procedure}", NameSource.UserInput, () => service.ExecuteAsync(Report(procedure, accountId)));
```

Check it in the database:

```sql
SELECT count(*)
FROM account
WHERE account_number = 'ACC-EA41';
```

Expect: 1: the table is there.

#### 3. A false positive, triaged

A false positive: an old client sends "account report (v1)". The parentheses make ARX flag it as SQL, the client retries, and after three strikes the user is blocked. Support reads the log, sees the same harmless name three times, unblocks the user, and the endpoint maps the old name to the procedure.

In scope: `service` (ArxMsSqlDbService), `guard` (SecurityGuard), `accountId` (ArxRecordId).

```csharp
for (var attempt = 0; attempt < 3; attempt++)
{
    await RunDirectlyAsync(service, guard, "olga", "account report (v1)", accountId);
}

Console.WriteLine($"olga blocked: {guard.IsBlocked("olga")}; logged: {guard.Events.Last(x => x.User == "olga").Detail}");

guard.Unblock("olga");
Reports["account report (v1)"] = "sp_account_report";
var mapped = await RunThroughTheListAsync(service, guard, "olga", "account report (v1)", accountId);

Console.WriteLine($"after triage: olga blocked: {guard.IsBlocked("olga")}, the old name runs: {mapped.Outcome}");
```

Defined in the sample, used above:

```csharp
private static readonly Dictionary<string, string> Reports = new(StringComparer.OrdinalIgnoreCase)
{
    ["account-report"] = "sp_account_report",
};

private static ArxProcedureExpression Report(string procedureName, ArxRecordId accountId)
{
    var report = new ArxProcedureExpression(procedureName);
    report.AddParameter("p_account_id", accountId.AsInt64());
    return report;
}

private static Task<GuardResult<ArxExecuteResult>> RunThroughTheListAsync(ArxMsSqlDbService service, SecurityGuard guard, string user, string report, ArxRecordId accountId)
    => Reports.TryGetValue(report, out var procedure)
        ? guard.RunAsync(user, $"report {report}", NameSource.Code, () => service.ExecuteAsync(Report(procedure, accountId)))
        : Task.FromResult(new GuardResult<ArxExecuteResult>(GuardOutcome.Refused, null, $"404 Not Found: no report '{report}'."));

private static Task<GuardResult<ArxExecuteResult>> RunDirectlyAsync(ArxMsSqlDbService service, SecurityGuard guard, string user, string procedure, ArxRecordId accountId)
    => guard.RunAsync(user, $"report {procedure}", NameSource.UserInput, () => service.ExecuteAsync(Report(procedure, accountId)));
```

Check it in the database:

```sql
SELECT COUNT(*)
FROM sys.procedures
WHERE name = 'sp_account_report';
```

Expect: 1: the procedure the old name now maps to.

### Execute on a preloaded, Strict schema: routines aren't covered, tables are

Advanced case 5 of Execute.

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.

Good to know:

A routine's parameters and result columns are the routine's, not a table's, so a preload doesn't list them and Strict doesn't check them. What a routine may touch is the database's business: grant EXECUTE only on the routines an endpoint may call.

Usings:

```csharp
using ARX.SDK;
using ARX.SDK.Query;
using ARX.SDK.SQL.Services;
using ARX.SDK.Services;
```

Set up by the sample before its scenarios:

```csharp
var account = new ArxEntity("account");
account["name"] = "Routine Roasters";
account["account_number"] = "ACC-PRE-EX5";
account["credit_limit"] = 2_500m;
var accountId = await service.CreateAsync(account);
var tables = new ArxPreLoadTableRegister()
    .PreLoadTable("account", ["name", "account_number", "status"]);
var schema = await new ArxPreLoadTable(tables).LoadAsync(connectionString);
var service = new ArxMsSqlDbService(connectionString, schema) { Strict = true };
```

#### 1. A procedure and a function on a Strict service

A procedure renames the account; a function counts its contacts. Neither is in the preload.

In scope: `service` (ArxMsSqlDbService), `accountId` (long).

```csharp
var rename = new ArxProcedureExpression("sp_rename_account");
rename.AddParameter("p_account_id", accountId);
rename.AddParameter("p_name", "Routine Roasters & Co");
await service.ExecuteAsync(rename);

var count = new ArxProcedureExpression("fn_contact_count") { ProcedureType = ArxProcedureType.Function };
count.AddParameter(accountId);
Console.WriteLine($"renamed through sp_rename_account; fn_contact_count says {(await service.ExecuteAsync(count)).Value<long>()} contacts");

var renamed = new ArxQueryExpression("account");
renamed.ColumnSet.AddColumns("name");
renamed.Criteria.AddCondition("account_number", ArxConditionOperator.Equal, "ACC-PRE-EX5");
Console.WriteLine($"read back through the preloaded columns: {(await service.RetrieveMultipleAsync(renamed)).Entities.Single()["name"]}");
```

Check it in the database:

```sql
SELECT name, CASE WHEN modified_on IS NOT NULL THEN 1 ELSE 0 END AS renamed
FROM account
WHERE account_number = 'ACC-PRE-EX5';
```

Expect: Routine Roasters & Co, 1.

#### 2. A function as a row source: the routine's columns

fn_accounts_with_status returns account rows: as a row source, every column it returns comes back.

In scope: `service` (ArxMsSqlDbService).

```csharp
var active = new ArxQueryExpression(ArxRowSource.Function("fn_accounts_with_status", "ACTIVE")) { ColumnSet = ArxColumnSet.All };
active.Criteria.AddCondition("account_number", ArxConditionOperator.Equal, "ACC-PRE-EX5");
var row = (await service.RetrieveMultipleAsync(active)).Entities.Single();
Console.WriteLine($"{row.Attributes.Count} columns, credit_limit {row["credit_limit"]:N2} among them");
```

Check it in the database:

```sql
SELECT account_number, credit_limit
FROM fn_accounts_with_status('ACTIVE')
WHERE account_number = 'ACC-PRE-EX5';
```

Expect: 1 row, 2500.00.

#### 3. The table is still an allow list

The table itself stays an allow list: credit_limit wasn't preloaded.

In scope: `service` (ArxMsSqlDbService).

```csharp
var probe = new ArxQueryExpression("account");
probe.ColumnSet.AddColumns("name", "credit_limit");

try
{
    await service.RetrieveMultipleAsync(probe);
}
catch (ArxValidationException exception) when (exception.Code == ArxErrorCode.NotPreloaded)
{
    Console.WriteLine(exception.Message);
}
```

Note: Nothing to check: the read was refused before the database (ARX1018).