TVPs from Blazor / PowerShell

Status: Reviewed (deep-dive v1)
Stack note: Multi-select filters and bulk ids should arrive as table-valued parameters into usp_* — not CSV strings or dynamic IN (...) (SQL-14). Deepens SQL-13 (table-var estimates; dump big TVPs to #temp).
Pairs with: SQL-12 (proc API), SQL-13 (#temp/TVP), SQL-16 (indexes after dump), SQL-19 (merge from staged sets).
Goal: Create types, pass from Blazor/PS, join safely, and know when to stage to #temp.

Quick chooser

Need	Prefer	Avoid
Blazor multi-select instances/alerts	TVP of ids	`string.Join(",", ids)` into SQL
PS bulk key list into merge	TVP or staging table	Row-by-row `INSERT` in a loop from PS
Few dozen ids	TVP join directly	Over-engineering
Tens of thousands of ids	TVP → `#temp` + index then join	Naked TVP join to huge fact tables
Reusable shape	Named `CREATE TYPE` in deploy scripts	Ad-hoc table vars only in one proc

SQL type + proc

CREATE TYPE dbo.IntIdList AS TABLE
(
  Id int NOT NULL PRIMARY KEY
);
GO

CREATE OR ALTER PROC dbo.usp_Inventory_GetInstancesByIds
  @Ids dbo.IntIdList READONLY
AS
BEGIN
  SET NOCOUNT ON;

  SELECT i.SqlInstanceId, i.HostName, i.InstanceName, i.LastSeenAt
  FROM dbo.SqlInstance i
  INNER JOIN @Ids x ON x.Id = i.SqlInstanceId
  ORDER BY i.HostName, i.InstanceName;
END;
GO

-- grants: EXECUTE on proc; TYPES need GRANT EXECUTE ON TYPE::dbo.IntIdList TO [AppRole];

String keys:

CREATE TYPE dbo.InstanceKeyList AS TABLE
(
  InstanceKey nvarchar(128) NOT NULL PRIMARY KEY
);

READONLY required on TVP params. You cannot modify @Ids inside the proc — copy to #temp if you must.

Large list pattern (estimates)

CREATE OR ALTER PROC dbo.usp_Alert_GetByIds
  @Ids dbo.IntIdList READONLY
AS
BEGIN
  SET NOCOUNT ON;

  CREATE TABLE #Ids (Id int NOT NULL PRIMARY KEY);
  INSERT #Ids (Id) SELECT Id FROM @Ids;

  SELECT a.AlertId, a.Title, a.Status, a.SeverityCode
  FROM dbo.Alert a
  INNER JOIN #Ids x ON x.Id = a.AlertId;
END;

Why: TVPs behave like table variables for cardinality — fine small; painful large against big boards (SQL-13).

Blazor / C# (shape)

// Build DataTable or use SqlParameter with structured type
var table = new DataTable();
table.Columns.Add("Id", typeof(int));
foreach (var id in selectedIds)
    table.Rows.Add(id);

var p = new SqlParameter("@Ids", SqlDbType.Structured)
{
    TypeName = "dbo.IntIdList",
    Value = table
};
// CommandType.StoredProcedure → usp_Inventory_GetInstancesByIds

Checklist:

[ ] TypeName matches SQL type (schema-qualified)
[ ] Column names/order match type definition
[ ] Empty TVP = valid “no rows” (decide UX: empty board vs treat as “no filter”)
[ ] App role: EXECUTE on type + proc
[ ] Never concatenate ids into CommandText

Empty vs null filter: if “no selection means all,” don’t send TVP path — call a different proc or pass a @FilterApplied bit.

PowerShell

# Example: DataTable → structured param with Invoke-Sqlcmd / SqlCommand
$dt = New-Object System.Data.DataTable
[void]$dt.Columns.Add("Id", [int])
foreach ($id in $IdList) { [void]$dt.Rows.Add($id) }

$cmd = $conn.CreateCommand()
$cmd.CommandText = "dbo.usp_Inventory_GetInstancesByIds"
$cmd.CommandType = [System.Data.CommandType]::StoredProcedure
$p = $cmd.Parameters.Add("@Ids", [System.Data.SqlDbType]::Structured)
$p.TypeName = "dbo.IntIdList"
$p.Value = $dt

Collectors with huge sets: bulk into *Stage then merge (SQL-19) often beats megabyte TVPs.

Estate uses

UI / job	TVP idea
Multi-select instances on boards	`IntIdList` / `InstanceKeyList`
Bulk ack (if ever allowed)	Id list — still one set UPDATE (SQL-15/18)
Exception allowlist edit	Keys to add/remove
PS “refresh these hosts”	Key list into merge stage
Security roster diff	Pair of TVPs or stage tables (SPIKE-06)

Deploy & versioning

Types ship in /sql/.../types.sql before procs that use them
Changing a type often means drop dependents → recreate — treat as breaking
Prefer additive new type (IntIdList2) over hot-rewriting in place
Document TypeName strings for Blazor so they don’t drift

Common failure patterns

Symptom	Likely cause
“TVP invalid” / metadata error	Wrong `TypeName` or column mismatch
Permission denied on type	Missing `GRANT EXECUTE ON TYPE`
Slow with 50k ids	No `#temp` dump; bad estimates
“All rows” when none selected	Empty TVP join returns empty — UI treated as all
Injection-looking code	Still building CSV “just this once”

Tiny lab

Create IntIdList; proc joins three ids — confirm from Blazor/PS
Pass empty TVP — confirm empty result
20k ids: direct TVP join vs #temp — compare plans (SQL-20)

Tie-ins

SQL-13: TVP = table-var semantics
SQL-14: TVP kills CSV IN dynamic SQL
SQL-12: Proc is the only Blazor surface

Done when

[ ] Shared id/key types in repo + grants documented
[ ] Multi-select boards use TVP procs (no CSV)
[ ] Large-list procs dump to #temp when needed
[ ] This note linked from Knowledge Base list

SQL Dude — SQL-21 TVPs from Blazor/PS v1