Back a few years ago, I was working on a large project with a group of developers, and one in particular – Craig Boland, a super-senior .Net/business logic layer programmer based in Dallas – comes up and says, “Hey man, I need to call this proc 1000 times in a row. Is there a way that I can call it only once and process all of these records?” I says, “No man, they haven’t invented table-valued parameters yet in SQL Server!” Craig walked away, head down and thoroughly disgusted with me. From that point, I thought to myself, “I’ve got to find this dude some way to do this.” But, before I could create some hack, they came up…table-valued parameters.
Table-valued parameters are awesome! Below is a minimal example (included are both the TSQL and c#) for the demo. Think I saw something like this in a Dell flyer, but can’t place the source. Doesn’t matter. Anyway, I pasted my TSQL first because it seems to make more sense. Essentially, what it does is take a data set, add it as a structured type, and then processes the entire record set. No need for multiple round trips, and no need for all of the overhead of firing over and over a SQL Server stored procedure. They also (BOL):
Do not acquire locks for the initial population of data from a client. Provide a simple programming model. Enable you to include complex business logic in a single routine. Reduce round trips to the server. Can have a table structure of different cardinality. Are strongly typed. Enable the client to specify sort order and unique keys.
I really think this is one of the coolest features in SQL Server 2008. And yeah it’s probably all over the internet, so consider this all over +1.
Enjoy!
Lee
-- Create table to hold items
DROP TABLE OrderDetail
GO
CREATE TABLE OrderDetail
(OrderId int
,ItemId int
,Quantity int
,SalesPersonId int
);
GO
DROP TYPE OrderLineItems
GO
-- Create table type. Acts like a struct, must be READ ONLY when instantiated
CREATE TYPE OrderLineItems as TABLE
(OrderId int
,ItemId int
,Quantity int
,SalesPersonId int
);
GO
DROP PROCEDURE __processItems
go
CREATE PROCEDURE __ProcessItems
@TVP OrderLineItems READONLY
AS
SET NOCOUNT ON
INSERT INTO OrderDetail
SELECT OrderId
,ItemId
,Quantity
,SalesPersonId
FROM @TVP;
GO
SELECT * FROM OrderDetail;
GO
So, in a nutshell, what we are doing here is creating the table type that will match the DDL of the destination table, and adding that type as an input parameter to the stored procedure that we will call. Simple! For the .Net code, don’t forget to change the server name when you fire this up in your environment.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Sql;
namespace TVP
{
class TVPdemo
{
public static void Main(string[] args)
{
DataTable dt = new DataTable("OrderItems");
dt.Columns.Add("OrderId", typeof(int));
dt.Columns.Add("ItemId", typeof(int));
dt.Columns.Add("Quantity", typeof(int));
dt.Columns.Add("SalesPersonId", typeof(int));
dt.Rows.Add(1, "100", 2, 1000);
dt.Rows.Add(1, "101", 15, 1001);
dt.Rows.Add(1, "102", 20, 1002);
SqlConnection conn = new SqlConnection("data source = S03777\\SQLEXPRESS2K8;" +
" Initial Catalog = TEST; Integrated Security = SSPI;");
conn.Open();
SqlCommand cmd = new SqlCommand("__ProcessItems", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter parm = new SqlParameter("TVP", SqlDbType.Structured);
parm.Value = dt;
cmd.Parameters.Add(parm);
cmd.ExecuteNonQuery();
conn.Close();
}
}
}
-----------------------------------
Snaps to the SQL team for this one!!! Niiiiiiiiiiiiice!
