Table-Valued Parameters

As the name implies, a table-valued parameter (TVP) lets you pass an entire set of rows as a single parameter to T-SQL stored procedures and user-defined functions (UDFs). This is extremely useful in and of itself, but arguably the most compelling facet of TVPs is their ability to marshal an entire set of rows across the network, from your .NET client to your SQL Server database, with a single stored procedure call (one roundtrip) and a single table-valued parameter.

Prior to SQL Server 2008, developers were forced to resort to clever hacks in an effort to reduce multiple roundtrips into one when inserting multiple rows—using techniques such as XML, delimited or encoded text, or even (gasp) accepting hundreds (up to 2100!) ...

Get Programming Microsoft® SQL Server® 2012 now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.