Table Valued Parameters

Passing multiple rows of data to stored procedures has always intrigued application developers. Many different methods have been used, more evolving around XML or some form of concatenated list. Those approaches require a method for parsing before the data can be used in normalized table format.

SQL Server 2008 adds another option: table valued parameters. This allows sending a table as parameter to the stored procedure. The current implementation still has shortcomings because it is a read-only parameter, and it cannot be used as output parameter.

Here is one example to demonstrate using table valued parameters to pass a table as stored procedure input.

CREATE TABLE Loans (
 loan_nbr INT PRIMARY KEY,
 loan_date DATETIME,
 loan_amount DECIMAL(15, 2));
 
-- User-defined table type 
CREATE TYPE LoanTable
AS TABLE (
 loan_nbr INT PRIMARY KEY,
 loan_date DATETIME,
 loan_amount DECIMAL(15, 2));
 
GO
 
-- Procedure with table valued parameter
-- Must use the READONLY clause
CREATE PROCEDURE InsertLoans 
 @Loans LoanTable READONLY
AS
 
 INSERT INTO Loans
 SELECT loan_nbr, loan_date, loan_amount
 FROM @Loans;
 
GO
 
-- Declare table variable of the new type
DECLARE @NewLoans LoanTable;
 
-- Initialize the table variable with data
INSERT INTO @NewLoans
VALUES (1, '20080101', 10000.00),
       (2, '20080203', 15000.00),
       (3, '20080315', 25000.00);
 
 
-- Insert new loans using the table variable as parameter
EXEC InsertLoans @Loans = @NewLoans;


The really cool part here is that table valued parameters are fully supported on the client side via ADO.NET (.NET Framework 3.5 or later) . That means we could easily have a data bound control like grid on the client side that can be used to feed a table parameter to update database table via a stored procedure. Here is a look at what that client code will be.

DataTable loans;
 
loans = new DataTable();
loans.Columns.Add("loan_nbr", typeof(int));
loans.Columns.Add("loan_date", typeof(System.DateTime));
loans.Columns.Add("loan_amount", typeof(decimal));
 
using(SqlCommand cmd = new SqlCommand("InsertLoans", conn)
{
  cmd.CommandType = CommandType.StoredProcedure;
  cmd.Parameters.AddWithValue("Loans", loans);
  cmd.ExecuteNonQuery();
}


Resources:

Table-Valued Parameters
http://msdn2.microsoft.com/en-us/library/bb510489(SQL.100).aspx

Labels: ,