Table-valued parameters

A table-valued parameter lets you pass an entire set of rows into a stored procedure as a single typed temporary table. This avoids dynamic SQL, IN-list size limits, and the overhead of multiple round trips.

This page walks through three TVP shapes — single-int, single-Guid, composite (int, Guid) — and a TVP combined with a scalar write.

SQL Server objects

-- 1. Schema for user-defined types
IF SCHEMA_ID(N'Types') IS NULL
    EXEC(N'CREATE SCHEMA Types AUTHORIZATION dbo;');
GO

-- 2. User-defined table types — one per column shape you need
CREATE TYPE Types.tvp_Int AS TABLE
(
    UserId INT NOT NULL
);
GO

CREATE TYPE Types.tvp_Guid AS TABLE
(
    UserGuid UNIQUEIDENTIFIER NOT NULL
);
GO

CREATE TYPE Types.tvp_IntGuid AS TABLE
(
    UserId   INT              NOT NULL,
    UserGuid UNIQUEIDENTIFIER NOT NULL
);
GO

-- 3. Stored procedures that consume each TVP shape
CREATE PROCEDURE Users.usp_Get_Users_From_TvpInt
    @tvp Types.tvp_Int READONLY
AS
BEGIN
    SET NOCOUNT ON;
    SELECT u.UserId, u.UserGuid
    FROM   Users.Users AS u
    INNER JOIN @tvp    AS t ON t.UserId = u.UserId
    ORDER BY u.UserId;
END
GO

CREATE PROCEDURE Users.usp_Get_Users_From_TvpGuid
    @tvp Types.tvp_Guid READONLY
AS
BEGIN
    SET NOCOUNT ON;
    SELECT u.UserId, u.UserGuid
    FROM   Users.Users AS u
    INNER JOIN @tvp    AS t ON t.UserGuid = u.UserGuid
    ORDER BY u.UserId;
END
GO

CREATE PROCEDURE Users.usp_Get_Users_From_TvpIntGuid
    @tvp Types.tvp_IntGuid READONLY
AS
BEGIN
    SET NOCOUNT ON;
    SELECT u.UserId, u.UserGuid
    FROM   Users.Users AS u
    INNER JOIN @tvp    AS t ON t.UserId = u.UserId AND t.UserGuid = u.UserGuid
    ORDER BY u.UserId;
END
GO

Create TVP row types

CaeriusNet generates the ITvpMapper<T> implementation when you apply [GenerateTvp]. The constructor parameters must match the SQL table type column order and types exactly:

using CaeriusNet;
using CaeriusNet.Commands;

[GenerateTvp(Schema = "Types", TvpName = "tvp_Int")]
public sealed partial record UsersIntTvp(int UserId);

[GenerateTvp(Schema = "Types", TvpName = "tvp_Guid")]
public sealed partial record UsersGuidTvp(Guid UserGuid);

[GenerateTvp(Schema = "Types", TvpName = "tvp_IntGuid")]
public sealed partial record UsersIntGuidTvp(int UserId, Guid UserGuid);

1. TVP read — filter by integer IDs

public async Task<IReadOnlyCollection<UserDto>> GetUsersByTvpIntAsync(
    CancellationToken ct)
{
    IReadOnlyCollection<UsersIntTvp> ids = [new(1), new(2), new(3), new(4)];

    var command = new StoredProcedureCommandBuilder(
            "Users", "usp_Get_Users_From_TvpInt")
        .WithResultSetCapacities(5)
        .AddTvpParameter("tvp", ids)   // matches @tvp in the stored procedure, without @
        .Build();

    return await DbContext.QueryReadOnlyCollectionAsync<UserDto>(command, cancellationToken: ct);
}

Telemetry tags

The client span identifies the stored procedure but does not include TVP values or a cache key.

2. TVP read — filter by GUIDs

public async Task<ImmutableArray<UserDto>> GetUsersByTvpGuidAsync(
    CancellationToken ct)
{
    IReadOnlyCollection<UsersGuidTvp> guids =
    [
        new(Guid.Parse("11111111-1111-1111-1111-111111111111")),
        new(Guid.Parse("33333333-3333-3333-3333-333333333333")),
    ];

    var command = new StoredProcedureCommandBuilder(
            "Users", "usp_Get_Users_From_TvpGuid")
        .WithResultSetCapacities(5)
        .AddTvpParameter("tvp", guids)
        .Build();

    return await DbContext.QueryImmutableArrayAsync<UserDto>(command, cancellationToken: ct);
}

3. TVP read — composite (int, Guid) key filter

public async Task<IEnumerable<UserDto>> GetUsersByTvpIntGuidAsync(
    CancellationToken ct)
{
    IReadOnlyCollection<UsersIntGuidTvp> pairs =
    [
        new(1, Guid.Parse("11111111-1111-1111-1111-111111111111")),
        new(2, Guid.Parse("22222222-2222-2222-2222-222222222222")),
    ];

    var command = new StoredProcedureCommandBuilder(
            "Users", "usp_Get_Users_From_TvpIntGuid")
        .WithResultSetCapacities(5)
        .AddTvpParameter("tvp", pairs)
        .Build();

    return await DbContext.QueryAsync<UserDto>(command, cancellationToken: ct);
}

4. TVP combined with a scalar write

You can mix TVP parameters with regular scalar parameters in the same call. Here the TVP carries the target user IDs and the scalars carry the order metadata:

CREATE PROCEDURE Users.usp_Create_Orders_For_Users
    @tvp    Types.tvp_Int READONLY,
    @Label  NVARCHAR(64),
    @Amount DECIMAL(10, 2)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO Users.Orders (UserId, Label, Amount)
    SELECT t.UserId, @Label, @Amount
    FROM   @tvp AS t;

    SELECT @@ROWCOUNT AS RowsInserted;
END
GO

public async Task<int> CreateBatchOrdersAsync(
    IReadOnlyCollection<int> userIds,
    string label,
    decimal amount,
    CancellationToken ct)
{
    if (userIds.Count == 0) return 0;

    IReadOnlyCollection<UsersIntTvp> targetUserIds = userIds.Select(static id => new UsersIntTvp(id)).ToArray();

    var command = new StoredProcedureCommandBuilder("Users", "usp_Create_Orders_For_Users")
        .AddTvpParameter("tvp",     targetUserIds)
        .AddParameter   ("Label",  label,  SqlDbType.NVarChar)
        .AddParameter   ("Amount", amount, SqlDbType.Decimal)
        .Build();

    return await DbContext.ExecuteScalarAsync<int>(command, ct);
}

Notes and constraints

• The TVP parameter name in AddTvpParameter("tvp", ...) must match the stored procedure @tvp parameter name without the leading @.
• READONLY is required on TVP parameters in SQL Server stored procedures.
• CaeriusNet sends TVP rows without requiring a DataTable.
• TVP rows and parameter values are not exported to telemetry. Capture of parameter values is disabled unless an explicit development allowlist/redaction policy is configured.
• Empty TVPs are supported on SQL Server 2022 and 2025. The generator declares immutable metadata and the runtime creates one reusable SqlDataRecord, so it never needs to infer metadata from a first row.
• IReadOnlyCollection<T> expresses a replayable input. For a one-pass producer, call AddTvpParameter("tvp", static ct => ProduceRows(ct)); it streams without materializing but the command cannot be cached.

---

Next: Multi-result sets - fetch multiple result sets in one round trip.