Table Types

The TableType class in Weasel.SqlServer.Tables manages user-defined table types, commonly used for table-valued parameters in stored procedures.

Defining a Table Type

var identifier = DbObjectName.Parse(SqlServerProvider.Instance, "dbo.OrderItemType");
var tableType = new TableType(identifier);

tableType.AddColumn<int>("product_id").NotNull();
tableType.AddColumn<int>("quantity").NotNull();
tableType.AddColumn("unit_price", "decimal(10,2)");

^{snippet source | anchor}

Column Configuration

AddColumn returns an ITableTypeColumn that supports:

• AllowNulls() -- column accepts NULL values
• NotNull() -- column rejects NULL values (default)

You can add columns by .NET type or by explicit database type string:

tableType.AddColumn<string>("name");            // maps to varchar(100)
tableType.AddColumn("notes", "nvarchar(max)");  // explicit type

^{snippet source | anchor}

Generating DDL

var migrator = new SqlServerMigrator();
var writer = new StringWriter();
tableType.WriteCreateStatement(migrator, writer);
// Output: IF TYPE_ID(N'dbo.OrderItemType') IS NULL, then CREATE TYPE dbo.OrderItemType AS TABLE (...)

^{snippet source | anchor}

CREATE TYPE has neither an OR ALTER form nor an IF NOT EXISTS clause, so the create statement leads with a TYPE_ID check. Without it a rendered migration script fails on its second run with "The type ... already exists", taking every statement after it down with it. See batch separators and re-runnable scripts.

Delta Detection

TableTypeDelta compares expected and actual column definitions by querying sys.table_types and sys.columns:

await using var conn = new SqlConnection(connectionString);
await conn.OpenAsync();

var delta = await tableType.FindDeltaAsync(conn);
// delta.Difference: None, Create, or Update

^{snippet source | anchor}

When an update is needed, the delta generates a DROP followed by CREATE since SQL Server does not support ALTER TYPE.

Using with Stored Procedures

Table types enable passing structured data to stored procedures:

CREATE PROCEDURE dbo.InsertOrderItems
    @Items dbo.OrderItemType READONLY
AS
BEGIN
    INSERT INTO order_items (product_id, quantity, unit_price)
    SELECT product_id, quantity, unit_price FROM @Items;
END;