Free SQL Help for Devs, DBAs, and the Curious

Table-Valued Parameters (TVPs) are a powerful feature in SQL Server that allow developers to pass sets of data to stored procedures and functions. Before the introduction of TVPs in SQL Server 2008, developers had to resort to complex workarounds, such as passing comma-separated strings or using temporary tables. With TVPs, these challenges are things of the past. They make the code cleaner, more efficient, and easier to maintain.

The first step in using TVPs is defining the table type. This is a user-defined type (UDT) that specifies the structure of the table that will be used as a parameter. Here is an example of creating a table type in SQL Server 2016/2017:

CREATE TYPE dbo.CustomerType AS TABLE
(
    CustomerID INT,
    CustomerName NVARCHAR(50),
    Address NVARCHAR(255)
);

Once the table type is created, you can use it as a parameter in a stored procedure or function. The following example demonstrates how to use the CustomerType parameter in a stored procedure:

CREATE PROCEDURE dbo.InsertCustomers
    @Customers dbo.CustomerType READONLY
AS
BEGIN
    INSERT INTO dbo.Customers (CustomerID, CustomerName, Address)
    SELECT CustomerID, CustomerName, Address 
    FROM @Customers;
END;

In this example, the stored procedure inserts all the rows from the @Customers parameter into the dbo.Customers table. Note that TVP parameters are READONLY, so you can't modify the data in the table parameter within the stored procedure or function.

While SQL Server 2016/2017 supports TVPs, MySQL 5.7 and DB2 11.1 do not. However, you can achieve similar functionality using temporary tables or JSON objects. In Azure SQL, TVPs are fully supported just like in SQL Server 2016/2017.

In conclusion, TVPs are a powerful tool for passing sets of data to stored procedures and functions in SQL Server and Azure SQL. They can greatly simplify and optimize your code, making it cleaner, more efficient, and easier to maintain. While MySQL and DB2 do not currently support TVPs, you can achieve similar functionality using other techniques.