Table valued parameters

It's a new T-SQL enhancements done in sql server 2008 which allows us to pass the table as parameters for our stored procedure. In the client server architecture we used to pass individual rows from the front end and its get updated in the backend. Instead of passing individual rows, Microsoft released a new enhancement referred to as table value parameters where they are providing a flexibility to pass the table as a parameter from the front end.


Features:
1. Processing speed will be comparitively very faster.
2. We have to declare the table as a Readonly one. DML operations cannot be done on the table.
3. From the front end we have to pass the data in the form of structures.
4. Reduces roundtrip to the server
5. Processes complex logics at a stretch in one single routine.


-- Am trying to create a table "EmployeeTable" with three fields.
CREATE TABLE EmployeeTable
(id int,
[name] varchar(100),
designation varchar(100))

-- Creating a stored procedure "TableValuedSampleProc" to insert the rows.
CREATE PROCEDURE TableValuedSampleProc (@id int, @name varchar(100),@designation varchar(100))
AS
BEGIN
insert into EmployeeTable values (@id,@name,@designation)
END
-- Executing the stored procedure
EXEC TableValuedSampleProc 1,'one','manager'
EXEC TableValuedSampleProc 2,'two','sr tlr'
EXEC TableValuedSampleProc 3,'three','tlr'
SELECT * FROM EmployeeTable
-- Am trying to create a table type "EmployeeTableType"
CREATE TYPE EmployeeTableType AS TABLE
(ID int, [name] varchar(100),designation varchar(100))
-- Creating the stored procedure in insert the data using Table type.
CREATE PROCEDURE EmployeeTableTypeProc (@EmployeeTempTable EmployeeTableType READONLY)
AS
BEGIN
INSERT INTO EmployeeTable
SELECT * FROM @EmployeeTempTable
END
-- Building a temporary table type
DECLARE @TempEmployee EmployeeTableType
INSERT INTO @TempEmployee VALUES (1,'one','manager')
INSERT INTO @TempEmployee VALUES (2,'two','sr tlr')
INSERT INTO @TempEmployee VALUES (3,'three','tlr')
-- Executing the stored procedure by passing the temporary table type
EXEC EmployeeTableTypeProc @TempEmployee
-- Checking the existence of data
SELECT * FROM EmployeeTable