\nThe June CTP of SQL Server 2008 contains support for table-valued parameters. Here's a usage scenario for these that has been around for a while.\n<\/p>\n
\nImagine you are running an online store and deal with (among other data) orders and order detail lines. You'd like to have a stored procedure that can add an entire order in one server round trip, regardless of the number of items that I order (that is, 1 order header and 1->n order detail lines). You'd even settle for two round trips, one for the order header, one for the details. Before SQL Server 2008, there is no built-in mechanism that supports this. In the past, I've seen some pretty interesting workarounds, such as:\n<\/p>\n
\n1. Compose an arbitrarily large SQL batch as a single "command text" by using string concatenation on the client\/middle-tier. All SQL Server database APIs support one (and only one) batch per Command. ADO classic did something like this when you inserted\/updated\/deleted multiple rows in a disconnected Recordset and called for a "batch update".\n<\/p>\n
\n2. Create a stored procedure with some "static" parameters and an arbitrarily large number of repeating parameters, most of which will always be NULL. The limit to the number of parameters in a stored procedure is 2100. Both this method and the previous one make for some pretty hideous-looking code.\n<\/p>\n
\n3. Use multiple parameter sets. OLE DB does support multiple parameter sets and some databases can optimize inserts that use multiple parameter sets. The SQL Server providers, at least last time I looked with SQL Profiler, turn multiple parameter sets into multiple calls, that is, one round trip per parameter set. That's not what I wanted.
\n
\nSQL Server 2008's solution to this age-old problem is table-valued parameters (TVPs, for short). You start using a TVP by creating a custom type, using the CREATE TYPE statement, like this:\n<\/p>\n
\nCREATE TYPE lineitem_type (line_number INT, order_id INT, product INT, quantity INT);\n<\/p>\n
\nInformation about these table types appear in sys.types and also in a new metadata view, sys.table_types. Once you've created such a table type, you can use it in T-SQL like this:\n<\/p>\n
\nCREATE PROC new_order (@order_id INT, @line_items lineitem_type)
\nAS
\n— silly table names used for clarity
\nINSERT INTO orders_table VALUES (@order_id … ) ;
\nINSERT INTO line_items_table
\n SELECT * FROM @line_items;\n<\/p>\n
\nOne round trip. Compact, clean code. Nice.<\/p>\n","protected":false},"excerpt":{"rendered":"
The June CTP of SQL Server 2008 contains support for table-valued parameters. Here's a usage scenario for these that has been around for a while. Imagine you are running an online store and deal with (among other data) orders and order detail lines. You'd like to have a stored procedure that can add an entire […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[29,40],"tags":[],"class_list":["post-790","post","type-post","status-publish","format-standard","hentry","category-sql-server-2008","category-transact-sql"],"yoast_head":"\n