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

Another powerful T-SQL enhancement in SQL Server 2008 is the ability to write composable DML. It allows to consume the OUTPUT clause result set and to feed it as source for a query.

Here is one example to demonstrate that. The scenario is to merge data from daily updated branch office table to central office static table. Additional requirement is to store auditing data for any changes in the company name column. In prior versions of SQL Server this would require a couple SQL statements to accomplish. In SQL Server 2008 the new MERGE statement allows to perform all merge actions in a single statement, and then feed via the OUTPUT clause data for the auditing table. That way a single SQL statement completes the task.

-- Create central accounts static table
CREATE TABLE CentralOfficeAccounts (
 account_nbr INT PRIMARY KEY,
 company_name VARCHAR(35),
 primary_contact VARCHAR(35),
 contact_phone VARCHAR(12));
 
-- Daily updated branch data
CREATE TABLE BranchOfficeAccounts (
 account_nbr INT PRIMARY KEY,
 company_name VARCHAR(35),
 primary_contact VARCHAR(35),
 contact_phone VARCHAR(12));
 
-- Insert sample data 
INSERT INTO CentralOfficeAccounts
VALUES (1, 'Bayside Motors', 'Jim Smith', '902-203-1234'),
       (2, 'Dallas Industries', 'Joe Doe', '301-663-9134'),
       (3, 'Sky Computer Systems', 'Jane Brown', '201-943-6053');
 
INSERT INTO BranchOfficeAccounts
VALUES (2, 'Dallas Industries, Inc.', 'Rick Gross', '301-663-9134'),    -- changed
       (3, 'Sky Computer Systems', 'Jane Brown', '201-943-6053'),       -- same
       (4, 'Auto Insurance Co.', 'Chris Jefferson', '313-601-6201');    -- new
 
-- Table for auditing
CREATE TABLE AccountsAudit (
 account_nbr INT,
 change_action NVARCHAR(10),
 change_date DATETIME DEFAULT CURRENT_TIMESTAMP,
 old_company_name VARCHAR(35),
 new_company_name VARCHAR(35),
 PRIMARY KEY(account_nbr, change_action, change_date));
 
-- Single SQL statement performing the merge and auditing actions
INSERT INTO AccountsAudit 
 (account_nbr, change_action, old_company_name, new_company_name)
SELECT account_nbr, merge_action, old_company_name, new_company_name
FROM (MERGE INTO CentralOfficeAccounts AS C    
      USING BranchOfficeAccounts AS B        
         ON C.account_nbr = B.account_nbr
      WHEN MATCHED                        
       AND C.company_name <> B.company_name THEN                        
           UPDATE SET C.company_name = B.company_name
      WHEN NOT MATCHED THEN            
           INSERT (account_nbr, company_name, 
                   primary_contact, contact_phone)
           VALUES (B.account_nbr, B.company_name, 
                   B.primary_contact, B.contact_phone)
     WHEN SOURCE NOT MATCHED THEN        
          DELETE
     OUTPUT $action, 
            COALESCE(inserted.account_nbr, deleted.account_nbr),
            deleted.company_name, 
            inserted.company_name
     ) AS T(merge_action, account_nbr, old_company_name, new_company_name);
 
SELECT account_nbr, change_action, change_date,
       old_company_name, new_company_name
FROM AccountsAudit; 
 
/*
 
-- Results
account_nbr change_action change_date old_company_name   new_company_name
----------- ------------- ----------- ------------------ -----------------------
1           DELETE        2008-04-15  Bayside Motors     NULL
2           UPDATE        2008-04-15  Dallas Industries  Dallas Industries, Inc.
4           INSERT        2008-04-15  NULL               Auto Insurance Co.
 
*/ 

Note:

This code has been tested with SQL Server 2008 CTP 6 (February 2008). As of SQL Server 2008 Release Candidate 0 (June 2008) the clause “WHEN SOURCE NOT MATCHED” has been replaced with “WHEN NOT MATCHED BY SOURCE”. This makes the wording clearer. It is good to note that this clause of MERGE is not standard.