SQL 2008 Table Values Parameters

Table valued parameters sample:

IF OBJECT_ID('Loans', N'U') IS NOT NULL
DROP TABLE Loans;

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
begin

   INSERT INTO Loans
   SELECT loan_nbr, loan_date, loan_amount
   FROM @Loans;

end

GO

-- Declare table variable of the new type

DECLARE @NewLoans LoanTable;
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 @NewLoans;

SELECT loan_nbr, loan_date, loan_amount
FROM Loans;

GO

/*
DROP PROCEDURE InsertLoans;
DROP TYPE LoanTable;
DROP TABLE Loans;
*/