Stored Procedures (Blazor API Surface)

Status: Reviewed (deep-dive v1)
Stack note: PowerShell collectors and Blazor Server should talk to SQL through usp_* procs — not ad-hoc SQL, not scalar UDFs as the API.
Pairs with: SQL-11 (UDFs stay inside T-SQL), SQL-13 (#temp staging in procs), SPIKE-01…08 board shapes.
Goal: Authoring, calling, security, and hygiene patterns for the maintenance warehouse API.

Quick chooser

Need	Do this
Blazor page needs data or an action	One proc per use-case (`usp_Alert_GetOpenBoard`, `usp_Alert_Ack`)
Reuse a query shape in many procs	View or inline TVF — not a second “API” UDF
Multi-step staging	Proc + `#temp` (SQL-13)
Filter list from UI	TVP or typed params — not concatenated SQL
Job / Agent automation	Same procs Blazor uses, or thin wrappers

Naming & layout

usp_<Area>_<Verb><Object>
  usp_Inventory_GetInstances
  usp_Backup_GetHeatmap
  usp_Alert_Evaluate          -- Agent-facing
  usp_Alert_Ack               -- UI-facing write

Schema: dbo or domain (alert, inv) — pick one convention and stick
Files in repo: /sql/<area>/00N_procs.sql (SPIKE layout)
No silent ALTER in prod — deploy via source control

Skeleton (every proc)

CREATE OR ALTER PROC dbo.usp_Example_GetBoard
  @SqlInstanceId int = NULL,
  @UnackedOnly bit = 0
AS
BEGIN
  SET NOCOUNT ON;
  SET XACT_ABORT ON;  -- optional but good for write procs

  -- reads: no outer tran needed
  SELECT …
  FROM …
  WHERE (@SqlInstanceId IS NULL OR i.SqlInstanceId = @SqlInstanceId)
    AND (@UnackedOnly = 0 OR a.Status = 'Open');
END;

Checklist on create:

[ ] SET NOCOUNT ON (cuts done-in-proc chatter; Blazor/ADO happier)
[ ] Explicit param types + defaults for optional filters
[ ] No SELECT * on wide warehouse tables — name columns the UI needs
[ ] Stable column aliases matching Blazor DTOs
[ ] Writes: TRY/CATCH + clear success/rowcount (see SQL-15 when published)

Read vs write procs

Kind	Examples	Notes
Read board	`GetOpenBoard`, `GetHeatmap`, `GetOutOfDate`	Filter params; ORDER BY for UI; no side effects
Read detail	`GetDetail @Id`	Single entity + child rows (events)
Command	`Ack`, `Evaluate`	Validate state; write + optional event row; no kill/revoke/patch from UI
Merge/load	Collector merge from PS	Idempotent upsert; run under Agent/PS credential

Hard rule (SPIKE): UI procs never start Agent jobs, revoke logins, or run backups — boards are observe + ack only.

Calling from Blazor / ADO

Prefer stored procedure command type + named parameters
Map result sets 1:1 to records/DTOs
Pass authenticated user into write procs (@AckedBy) — don’t trust a client-only field without auth context
Timeouts: board reads short; Evaluate may need longer (or Agent-only)
Connection: app pool login with EXECUTE on specific procs — not db_owner

// shape only — CommandType.StoredProcedure, parameters for @UnackedOnly etc.

Calling from PowerShell

Invoke-Sqlcmd -ServerInstance $Wh -Database $Db `
  -Query 'EXEC dbo.usp_Alert_Evaluate'  # or AddWithValue params for filters

Collectors: bulk load staging → usp_*_Merge. Don’t embed giant scripts in PS when a proc owns the logic.

Parameters & TVPs

Optional filters: NULL means “all” (document it)
Multi-id from Blazor: TVP (READONLY) — large lists → dump to #temp then join (SQL-13)
Avoid CSV strings + dynamic SQL for filters (SQL-14 when you pick it)
Output params rare; prefer result sets for Blazor

Security

[ ] GRANT EXECUTE ON dbo.usp_… TO [AppRole] — least privilege
[ ] Separate read vs write roles if possible (board vs ack)
[ ] Evaluate / merge: Agent or ops role only
[ ] Ownership chaining: keep tables + procs same schema owner; no dynamic SQL unless signed/careful
[ ] Secrets never returned in result sets

Performance habits

[ ] Sargable WHERE; indexes for board filters (SQL-16 shortlist)
[ ] Stage big intermediate sets in #temp, not table vars
[ ] One result set when UI expects one; document if multiple
[ ] Watch parameter sniffing on skewed estates — OPTION (RECOMPILE) or local vars only when measured
[ ] Query Store: baseline after deploy of hot usp_*

Common failure patterns

Symptom	Likely cause
Blazor timeout on board	Fat join / missing index / `Evaluate` called from UI thread
Extra empty result sets	Missing `SET NOCOUNT ON` or debug `SELECT` left in
Wrong user on Ack	`@AckedBy` not from auth; client spoof
Works in SSMS, fails in app	Different login rights; default DB; ansi settings
“API” is a scalar UDF	Wrong layer — wrap as proc

Estate map (SPIKE → proc)

Area	Example procs
Inventory	`usp_Inventory_GetInstances`, merge from PS
Backup	`usp_Backup_GetHeatmap`
Agent	`usp_Agent_GetFailBoard`
Alerts	`usp_Alert_Evaluate`, `usp_Alert_GetOpenBoard`, `usp_Alert_Ack`
Capacity / perf / security / build	Same Get* board pattern

Done when

[ ] Team rule: Blazor/PS → procs only for SQL access
[ ] Naming + SET NOCOUNT + grant checklist in PR template
[ ] This note linked from Knowledge Base list

SQL Dude — SQL-12 Stored Procedures v1