Example: SQL document

A sql/SQL-NNN.md page documents a single SQL statement or stored procedure call: how it is executed, what it takes, what it touches, and the actual SQL text. Doc gen produces one per SQL ID discovered during database analysis, for any repository type that talks to a database.

What reviewers should check

• The Classification table uses the template's exact field names — SQL ID, Operation type, Execution method, Database, Schema, Command type, Business object, Criteria property, Lifecycle method — and nothing invented.
• The Schema row is present and plausible for the target platform. A hallucinated schema name is the failure mode to watch for; confirm it against the connection string or the database project.
• Parameters are the real names from the code, with the platform's prefix (@Name for SQL Server, :Name for Oracle), and the list matches the AddParm calls in source.
• The Sample SQL is real, multi-line T-SQL or PL/SQL. A single flattened line, a generic SELECT * FROM Table, or a summary sentence in place of SQL is a defect.
• Application-layer SQL uses N/A (Application layer) for the business object rows and adds an Application class row, rather than inventing a business object.
• Every table row links into database-tables.md with an anchor that resolves, and ## Sources is the final section.

Example

The document below is a complete, correct SQL-007.md.

View rendered example — the same document as a real page, so you can compare rendering as well as source.

---
title: "SQL-007: Retrieve pending premium audits for a processing window"
summary: Reads the premium audit queue for audits due within a period, optionally narrowed to one policy.
status: draft
owner: Premium Audit Engineering
last_reviewed: 2026-08-14
library: SAIF.PremiumAudit.Batch
reference_type: embedded
---

# SQL-007: Retrieve pending premium audits for a processing window

This is the queue read that starts every premium audit batch run. The scheduled
job resolves its processing window from configuration, then calls
`dbo.usp_GetPendingAudits` to obtain the set of audits whose due date falls in
that window and whose status has not yet advanced past `Pending`. The optional
`@PolicyNumber` argument supports the operator-initiated single-policy rerun
described in the operational runbook; when it is omitted the procedure returns
the full window. Rows are returned ordered by due date so that the oldest
backlog is worked first, and the procedure applies its own `NOLOCK`-free read
because the queue table is written concurrently by the policy system.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-007 |
| **Operation type** | Read |
| **Execution method** | Stored procedure |
| **Database** | SQL Server |
| **Schema** | `dbo` |
| **Command type** | StoredProcedure |
| **Business object** | N/A (Application layer) |
| **Criteria property** | N/A |
| **Lifecycle method** | N/A |
| **Application class** | `PendingAuditRepository` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `@AuditPeriodStart` | Input | DateTime | N/A | Inclusive start of the processing window; audits with an earlier due date are excluded. |
| `@AuditPeriodEnd` | Input | DateTime | N/A | Inclusive end of the processing window; supplied as the run date plus the configured look-ahead days. |
| `@PolicyNumber` | Input | VarChar | 12 | Optional single-policy filter for operator-initiated reruns. Pass `NULL` to process the whole window. |
| `@ReturnValue` | ReturnValue | Integer | N/A | Count of queue rows returned; `0` indicates an empty window and causes the batch to exit with return code 3. |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [PremiumAuditQueue](../database-tables.md#premiumauditqueue) | SELECT | `AuditQueueId`, `PolicyNumber`, `AuditDueDate`, `AuditStatusCode`, `AttemptCount`, `LockedByProcess` |
| [Policy](../database-tables.md#policy) | JOIN | `PolicyNumber`, `PolicyEffectiveDate`, `PolicyExpirationDate`, `ServicingBranchCode` |
| [AuditStatus](../database-tables.md#auditstatus) | JOIN | `AuditStatusCode`, `AuditStatusName`, `IsTerminal` |

## Sample SQL

```sql
-- Stored procedure call issued by PendingAuditRepository.GetPendingAudits
EXEC dbo.usp_GetPendingAudits
  @AuditPeriodStart = @AuditPeriodStart,
  @AuditPeriodEnd = @AuditPeriodEnd,
  @PolicyNumber = @PolicyNumber
-- Procedure body resides in the SQL Server database and is version-controlled
-- in db/Procedures/usp_GetPendingAudits.sql
```

```sql
-- Procedure body, from db/Procedures/usp_GetPendingAudits.sql
SELECT
  q.AuditQueueId,
  q.PolicyNumber,
  q.AuditDueDate,
  q.AuditStatusCode,
  q.AttemptCount,
  p.PolicyEffectiveDate,
  p.PolicyExpirationDate,
  p.ServicingBranchCode,
  s.AuditStatusName
FROM dbo.PremiumAuditQueue AS q
  INNER JOIN dbo.Policy AS p
    ON p.PolicyNumber = q.PolicyNumber
  INNER JOIN dbo.AuditStatus AS s
    ON s.AuditStatusCode = q.AuditStatusCode
WHERE q.AuditDueDate >= @AuditPeriodStart
  AND q.AuditDueDate <= @AuditPeriodEnd
  AND s.IsTerminal = 0
  AND q.LockedByProcess IS NULL
  AND (@PolicyNumber IS NULL OR q.PolicyNumber = @PolicyNumber)
ORDER BY
  q.AuditDueDate ASC,
  q.AuditQueueId ASC
```

## Operations and processes
- [Load pending audit queue](../operations/load-pending-audit-queue.md)
- [Rerun single policy audit](../operations/rerun-single-policy-audit.md)

## Business rules
- [BR-AUDIT-004 Audits are processed oldest due date first](../business-rules.md#br-audit-004)
- [BR-AUDIT-011 Terminal audit statuses are never reprocessed](../business-rules.md#br-audit-011)
- [BR-AUDIT-012 Queue rows locked by another process are skipped](../business-rules.md#br-audit-012)

## Sources
- src/SAIF.PremiumAudit.Batch/Data/PendingAuditRepository.vb
- src/SAIF.PremiumAudit.Batch/Data/SqlCommandFactory.vb
- db/Procedures/usp_GetPendingAudits.sql