SQL Server 2008 - Table Type Parameter

This is very neat feature in SQL Server 2008 which many people really waited for...

now SQL Server 2008 has got a table type which can be passed as parameters in functions and stored procedures.

simplest example.. lets create employees table.

CREATE TABLE dbo.Employee(
EmpID int NOT NULL,
EmpName nvarchar(100) NOT NULL,
EmpEmail nvarchar(100) NOT NULL)


and create stored procedure to add new employees..

CREATE PROCEDURE NewEmployeeMS(@EmpID int,@EmpName nvarchar(100),@EmpEmail
nvarchar(100))
As
BEGIN
INSERT INTO dbo.Employee
values(
@EmpID, @EmpName, @EmpEmail)


now if I have to add record in it.. I have to execute this procedure multiple times..

now lets take example.. that I got records in another table and I have to get records from that and put it in another table.. I will go about doing it this way..

CREATE TYPE EmployeeTableType AS TABLE
(EmpID INT, EmpName nvarchar(100), EmpEmail nvarchar(100))

creating table type.. and when I create stored procedure it will take parameter as table type only..

CREATE PROCEDURE NewEmployee(@EmployeeDetails EmployeeTableType READONLY)
As
BEGIN
INSERT INTO dbo.Employee
SELECT * FROM @EmployeeDetails
END


notice READONLY thing in parameter.. it says that table is going to be read only.. so no any update, insert, delete will be performed.. just select.. and this thing is necessary.

finally I will do this..

DECLARE @NewEmployees EmployeeTableType
INSERT INTO @NewEmployees
VALUES(1,'John McLean','JohnMcLean@contoso.com')
INSERT INTO @NewEmployees
VALUES(2,'Bob Smith','BobSmith@contoso.com')
INSERT INTO @NewEmployees
VALUES(3,'Ted Connery','TedConnery@contoso.com')
EXECUTE NewEmployee @NewEmployees