Using Table-Valued Parameters to Update Multiple Rows

In previous versions of SQL Server, if I needed to update numerous rows of data, I used a temporary table. Suppose, for example, that thousands of transactions needed their transaction dates updated. I would upload the transaction IDs with the new date value in a temporary table, add a join between the transaction table and the temporary table, and update the transaction table with the new values from the temporary table.   

Although it seemed relatively simple to update thousands of records with temporary tables, what if I had thousands of transactions that needed multiple field values updated? Or what if the updating became a regular request and the company needed to have the functionality available in the application? The process would be difficult to implement and would have a performance impact on the database. With table-valued parameters, available in SQL Server 2008, the update process is easy to implement and has little performance impact on the database. Table-valued parameters can be passed as read-only input parameters. Because transaction logs are not recorded for table-valued parameters, performance improves. Previously, when multiple values were involved, the insert statement was executed multiple times; now, I use a table-valued parameter—with numerous values—that’s treated as one value and requires only one insert statement. The reduced number of trips between client and server boosts performance.

Creating and Using Table-Valued Parameters in T-SQL

A table-valued parameter is a new type of stored-procedure capability available in SQL Server 2008. Basically, a table-valued parameter lets you use an array of data in T-SQL, as well as send an entire data-set table as a parameter in a stored procedure or function. SQL Server 2008 stores this parameter as a user-defined table type. You can also add primary key and unique constraints to the table type.

The ddl_script.sql in Listing 1 sets up the test database MovieRentalDB with a table and the table type. Additionally, it populates the table with values.

The three steps that follow, each with its code segment, let you see how to create and execute table-valued parameters in T-SQL. First, with the code segment below, you create a new user-defined table type.

Second, you create a stored procedure with a table-valued parameter, as Listing 2 shows. Third, you execute the stored procedure, as Listing 3 shows. Figure 1 shows the results of the insert statement in Listing 3.

Note: The storedprocedure_script.sql is the insert procedure that passes the table-valued parameter with the matthewharrisapplication. Book3.csv, which you can download, is an example comma-delimited file of the values that will be passed with the stored procedure. Matthewharrisapplication is developed in C# and is also available for download. The application will work if the database is created on the local server. If not (if the database is created on a different instance of SQL Server than local server), SQLConnection will have to be changed.