How to use table-valued parameter in SQL Server and What is the use of table-valued parameter in SQL Server?

Rule 1:- Creating the simple query to insert the record in #TestEmpRecord table

Create table #TestEmpRecord

(

       Row_No int identity,

       EmpID varchar(10),

       EmpName varchar(50)

)

insert into #TestEmpRecord(

EmpID,EmpName

)

select 'E001','Employee 1' union

select 'E002','Employee 2' union

select 'E003','Employee 3' union

select 'E004','Employee 4' union

select 'E005','Employee 5' union

select 'E006','Employee 6'

Rule 2:- Creating the table-valued parameter and store procedure to insert the record in #TestEmpRecord table

Step 1:- Create a table type.

Create type dbo.[TestEmpRecord_type]

As table(

       EmpID varchar(10),

       EmpName varchar(50)

)

Step 2:- Create a procedure to receive data for the table-valued parameter.

Create proc TestEmpRecord_Insert

@TestEmpRecord TestEmpRecord_type readonly

as

begin

       insert into #TestEmpRecord(

       EmpID,EmpName

       )

       select EmpID,EmpName

       from @TestEmpRecord

end

Step 3:- Declare a variable that references the type

Declare @Data TestEmpRecord_type

Step 4:-Add data to the table variable

Insert into @Data(

EmpID,EmpName

)

select 'E001','Employee 1' union

select 'E002','Employee 2' union

select 'E003','Employee 3' union

select 'E004','Employee 4' union

select 'E005','Employee 5' union

select 'E006','Employee 6'

Step 5:- Pass the table variable data to a stored procedure.

Exec TestEmpRecord_Insert @Data

Note:-

1. Commonly table-valued parameter used for pass the large data.

2. Table-valued parameters must be passed as input READONLY parameters.

3. You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter

4. table-valued parameter must equal to source table.