Using Table Value Parameters in SQL Server 2008

Technorati Tags: SQL Server 2008,Table Value Parameters,.NET

Table-valued parameter is another exciting new feature in SQL Server 2008. Essentially, TVP solves the problem of inserting multiple rows into the database. Various solutions have been implemented to achieve this, ranging from round trips for every row to comma separated values. With TVP, you can insert multiple rows with a single round trip to the server without having the additional overheads of composition and de-composition of values.

TVP is not entirely new to SQL Server. SQL Server 2000 introduced the table variables which can be used to store a set of records. Where TVP builds on it, is the ability to pass them as input parameters to stored procedures or functions. This could not be done with table variables.

Let us look at some of the features of TVP before we see how they can be used to insert data.

• TVPs can participate in Set based operations.
• They are strongly typed
• You cannot perform DML operations on TVPs. They can be passed as only READ-ONLY parameters to stored procedures\functions
• Remember that TVPs are materialized in the TempDB. Essentially, it means that if you insert more rows into your TVP, the size of TempDB is what gets affected.

Having seen what TVP is, let us now move onto build an example which will actually use TVP. Here, I will show you a very common scenario that we use for multiple entries. We have an order and each order is comprised of multiple products. I have created the necessary tables using the scripts below

   1: Create table dbo.Products

   2: (

   3: ProductId int IDENTITY(1,1) PRIMARY KEY,

   4: ProductName varchar(250),

   5: ItemRate decimal

   6: )

   7:  

   8: Create table dbo.Orders

   9: (

  10:     OrderId int IDENTITY(1,1) Primary key,

  11:     OrderDate datetime,

  12:     OrderStatus int

  13: )

  14:  

  15: Create Table dbo.OrderDetails

  16: (

  17:     OrderId int Foreign Key references Orders(OrderId),

  18:     ProductId int Foreign Key references Products(ProductId),

  19:     Quantity int,

  20:     Amount decimal

  21: )

I have also inserted data for the tables above. The next thing to do is to create a user defined type that will insert data into the OrderDetails table.

   1: Create type dbo.OrderParam as Table

   2: (ProductId int, Quantity int, Amount decimal)

Now that the type has been created, let me go ahead and create a stored procedure that takes the just created type as an input parameter.

   1: Create proc dbo.InsertOrders

   2: @OrderItems OrderParam ReadOnly

   3: as

   4: begin

   5:     Declare @iOrderId int

   6:     Insert into Orders values (GETDATE(),0)

   7:     Select @iOrderId=SCOPE_IDENTITY()

   8:     Insert into OrderDetails(OrderId, ProductId, Quantity, Amount)

   9:         Select @iOrderId, ProductId, Quantity, Amount from @OrderItems

  10: end

Notice the fact that the TVPs have been declared read only. Also pay attention to the second insert statement. The values are inserted from the input parameter using a single select statement. The only remaining thing to do is to call this stored procedure from the front end.

For this purpose, I have created a data grid where users select the orders and save it into the database. Below is the code that goes into the Save Click routine.

   1: private void SaveButton_Click(object sender, EventArgs e)

   2:         {

   3:             //create a new datatable to hold the grid data

   4:             DataTable OrderParam = new DataTable();

   5:             //the columns are the same as the TVP type

   6:             OrderParam.Columns.Add("ProductId", typeof(int));

   7:             OrderParam.Columns.Add("Quantity", typeof(int));

   8:             OrderParam.Columns.Add("Amount", typeof(decimal));

   9:  

  10:  

  11:             //loop through the grid to load data into the datatable

  12:             foreach (DataGridViewRow item in grdOrders.Rows)

  13:             {

  14:                 DataGridViewComboBoxCell prodCell = (DataGridViewComboBoxCell) item.Cells[0];

  15:                 if (prodCell.Value != null)

  16:                 {

  17:                     DataRow prdRow = OrderParam.NewRow();

  18:                     prdRow[0] = prodCell.Value;

  19:                     prdRow[1] = item.Cells[1].Value;

  20:                     prdRow[2] = item.Cells[2].Value;

  21:                     OrderParam.Rows.Add(prdRow);

  22:                 }

  23:             }

  24:             //connect to the database to insert the values.

  25:             var connString = ConfigurationManager.ConnectionStrings["DatabaseConn"].ConnectionString;

  26:             SqlConnection dbConn = new SqlConnection(connString);

  27:             dbConn.Open();

  28:             using(SqlCommand cmd = new SqlCommand("InsertOrders", dbConn))

  29:             {

  30:               cmd.CommandType = CommandType.StoredProcedure;

  31:               cmd.Parameters.AddWithValue("OrderItems", OrderParam);

  32:               cmd.ExecuteNonQuery();

  33:             }

  34:             dbConn.Close();

  35:         }

The only thing worth noticing is that I have used a datatable which is quite logical because TVPs are tables anyways. As you can see, the savings in the number of round trips to the database is enormous. The USP of the TVPs lie in the fact that they are not hard to implement at all.  Have fun!!!