When working with any technology, I want to take advantage of everything that makes that technology unique. SQL Server is a powerful technology, but features can hide behind abstractions created by developers.

In this blog post, I’ll tell you what table-valued parameters are, how to use them in your calls to SQL Server, some caveats, and how to use them from your .NET Application.

What Is A User-Defined Table Type

SQL Server is a relational database, and in many instances, as a developer, we will be dealing with tables, columns, and rows. The table structure allows us to express the idea of “many” when outputting results. SQL Server enables us to create tables as containers for information, and these are known as user-defined table types. Developers commonly use these types as input parameters to stored procedures and user-defined functions.

If you want to see if any of your current databases have any user-defined table types, you will need to go to the following structure in your database.

Programmability > Types > User-Defined Table Types

Creating a User-Defined Table Type

Creating a user-defined table type is as simple as creating a regular table.

CREATE TYPE FunTableType 
   AS TABLE
      ( ActivityName VARCHAR(50)
      , Rating INT );

Here I defined a type of FunTableType, which we will use in a simple stored procedure that echoes our rows back.

/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE dbo.sp_EchoFun
   @TVP FunTableType READONLY
      AS
      SET NOCOUNT ON
      select * from @TVP

Finally, let’s call our stored procedure.

/* Declare a variable that references the type. */
DECLARE @activities AS FunTableType;
/* Add data to the table variable. */
INSERT INTO @activities (ActivityName, Rating) Values ('swimming', 5)
  
/* Pass the table variable data to a stored procedure. */
EXEC sp_EchoFun @activities;

We are creating our user-defined table in memory, inserting values into it, then calling our stored procedure. Putting it all together, you can see the results.

When Is This Useful

So when would you want to use a user-defined table? In my personal experience, the following scenarios provide the most value.

1. Input parameters into stored procedures that need to take in logical concepts as rows.
2. Inserting multiple rows more efficiently without resorting to SqlBulkCopy.

There is considerable overhead to using this feature, so use it judiciously. I explain some caveats in the next section.

Table-Valued Caveats

Before you start using user-defined table types, you should consider their strengths and weaknesses.

1. User-defined tables cannot be used to define columns in other database tables.
2. User-defined tables require permission to access and use them in stored procedures.
3. As the name implies, the data needs to be tabular.
4. Managing user-defined tables is like any other schema-based object.
5. Depending on your implementation, building the parameter in your codebase can be cumbersome.

With SqlCommand and SqlParameter

If you are not a fan of frameworks or libraries, you can use user-defined tables using ADO.NET.

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

var table = new DataTable();
table.Columns.Add("ActivityName", typeof(string));
table.Columns.Add("Rating", typeof(int));

var row = table.NewRow();

row["ActivityName"] = "swimming";
row["Rating"] = 1;

table.Rows.Add(row);

var command = connection.CreateCommand();
command.CommandText = "dbo.sp_EchoFun";
command.CommandType = CommandType.StoredProcedure;

var parameter = command.CreateParameter();
parameter.TypeName = "dbo.FunTableType";
parameter.Value = table;
parameter.ParameterName = "@TVP";

command.Parameters.Add(parameter);

var reader = await command.ExecuteReaderAsync();

var result = new DataTable();
result.Load(reader);

There are a couple of notable parts of this implementation:

1. We need to use the DataTable type to construct our table in-memory.
2. We need to add the rows to the data table.
3. The command is of type CommandType.StoredProcedure. This type isn’t necessary, but if used, make sure parameter names match your stored procedure definition.
4. The parameter TypeName is that of the user-defined table type in the database. In our case, it is dbo.FunTableType.

For simplicity, I just reloaded the result set back into a DataTable. You can see the results here.

With Dapper

You can make your code more accessible to other developers by using a library like [Dapper][]. Here is an example of using table-valued parameters with the Micro-ORM Dapper.

public class Fun
{
    public string ActivityName { get; set; }
    public int Rating { get; set; }
}

async static Task Main(string[] args)
{
    await using var connection = new SqlConnection(connectionString);
    await connection.OpenAsync();

    var table = new DataTable();
    table.Columns.Add("ActivityName", typeof(string));
    table.Columns.Add("Rating", typeof(int));
    var row = table.NewRow();
    row["ActivityName"] = "swimming";
    row["Rating"] = 1;
    table.Rows.Add(row);

    var result = await connection.QueryAsync<Fun>("EXEC sp_EchoFun @activities", new
    {
        activities = table.AsTableValuedParameter("dbo.FunTableType")
    });
}

Let’s look at the result.

Things to note in this example:

1. We still need to make a DataTable.
2. We call AsTableValuedParameter to convert our table into a parameter.
3. We still need to tell Dapper what the database type is.
4. Results are serialized directly into our C# object.

We could make this codebase even more friendly by creating a helper method that converts C# objects into DataTables.

listOfActivities.ToDataTable();
listOfActivities.ToTableValuedParameter("dbo.FunTableType");

Conclusion

Table-valued parameters and user-defined table types are a powerful combination. As a .NET developer, it can help you solve some complex problems while still being performance-minded. I hope you found this blog post enlightening and feel free to leave any comments.

Some referenced material:

• Microsoft Documentation #1
• User-Defined Tables