Table as Input Parameter of Stored Procedure using Table-Valued Parameters in SQL

Table as Input Parameter of Stored Procedure using Table-Valued Parameters in SQL

Table-Valued Parameters are used as Input parameter of stored procedure, For that first we need to create Table type.Table Type are best used when we want to insert multiple records in a table using a stored procedure table valued Parameter and executing the procedure once.

Table valued Parameters  is a new parameter type in SQL SERVER 2008 that provides efficient way of passing the table type variable than using the temporary table.

Below is the example showing how to take table as Input Parameter of Stored Procedure using table valued Parameter in SQL

In order to explain this I will create a table, Table type and a Stored Procedure with table valued parameter.

--table Script

create table Emp

(

 Empid nvarchar(30),

empName nvarchar(30)

)

--Script to create a Table Type with Name Employee 

Create type Employee as Table

( Empid nvarchar(30),

empName nvarchar(30)

)

Note: The scope of Table Type is limited to Batch only and Input parameter of procedure should be read only when using Table Type as Input Parameter of Stored Procedure.

Now we will create a stored Procedure and we will define input parameter of stored procedure

as table type

--Script to  create a procedure with Input parameter table type

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

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

To execute this Procedure we will declare a variable and will pass the variable to insert multiple rows to Emp table in a single execution of Procedure as below.

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')

--Executing Procedure

Exec   proc_x @x

Verify Output

select * from Emp 

Output

Empid empName

1 rack

2 mack

3 Rex

4 Peter