Table Type/Table Valued Parameter in SQL 2008

            Table Type/Table Valued Parameter in SQL 2008

Today I will Introduce you with a new concept in SQL which was not in Earlier version of SQL

ie SQL 2000 and SQL 2005

By Creating a table Type you don't have to write the table defination in Table variable,

Input Parameter of Stored Procedure and in Functions also.

Once you will create a table type you no need have to write the table defination 

Let me Explain you through an Example

--Below Script will create a Table Type with  Name Employee 

create type Employee as Table

( Empid nvarchar(30),empName nvarchar(30))

--Now You can use this table type for table variable based on table type

--you dont have to write the code again

You can simply Write

declare @employee as Employee;

-insert into @employee values ('1','xyz'),('2','abc')

select * from @employee 

Table Type has prevent you to again define the Table Variable and it will reduce the lenght of code when using will large code

Table Type can also used with Input Parameters of Stored Procedures which is extremely Useful

How Table Type can be used as a Input parameter Let me Explain through Example

Suppose you want to insert in a table Emp Multiple Records using a Stored Procedures

then you have to execute your procedures Multiple times

But with Table Type you can execute multiple record by executing the procedure once

For that we will create a table:

--table Script

create table Emp

(

 Empid nvarchar(30),

empName nvarchar(30)

)

If you are writing this in a new session again Execute the Table Type code as Table Type are limited to batch only

--Below Script will create a Table Type with  Name Employee 

create type Employee as Table

( Empid nvarchar(30),empName nvarchar(30))

Now we will insert multiple record in Emp table Using a Stored Procedure and input parameter as table type

-- create a procedure

create procedure proc_x

 @x as employee readonly

as 

begin 

insert into Emp 

select * from @x;--this will insert record from table variable @x into Emp table 

end

This Procedure will take table variable @x as input type which is using table type Employee 

To  execute this Procedure we will 

Declare @x as Employee 

--will insert all values in table varirable @x using insert script which will insert all record into Emp table

insert into @x values('1','rack'),('2','mack'),('3','Rex'),('4','Peter')

Exec   proc_x @x

Run this query to check your result

select * from Emp 

Output

Empid empName

1 rack

2 mack

3 Rex

4 Peter

Note the DataType fo Input parameter of procedure should be  Read Only when using Table Type as Input Parameter of Stored Procedure