Passing Table-valued Parameter to Stored procedure

Table-Valued Parameters:

                Table valued parameter provide the developer an easy way to work with Multiple rows and data. Table-Valued parameters were introduced in Sql Server 2008

Syntax:

create type TypeName AS TABLE(

column1 type,

column2 type...

)

Eg: create type EmployeeDetails AS TABLE(

RowID int,

EmployeeName varchar(25),

EmployeeTitle varchar(30),

EmployeeAge int

)

In the above eg,

We are creating a table valued type for EmployeeDetails,this table valued type replicate the same of a Sql Table.

Manipulation on Table Valued Type:

     Table valued parameters are same as Sql Table.

You can insert into table valued Type as same like how you insert value to a table.

Declare @EmployeeDt as EmployeeDetails

insert into @EmployeeDt values(1,'George','Manager',24)

insert into @EmployeeDt values(2,'Rahul','Tester',38)

You can select the entire data from that table-valued type by

a single select statement.

select * from @EmployeeDt

Passing Table-Valued type to StoredProcedure:

create procedure SaveEmployeeDetails

@EmployeeDt as EmployeeDetails Readonly

AS

Begin

--selecting from table valued parameter and inserting into table --in a single strech

insert into Tbl_Employee(ID,EmployeeName ,EmployeeTitle,EmployeeAge)

select  RowID,EmployeeName ,EmployeeTitle,EmployeeAge

from @EmployeeDt

End

C# Code:

SqlDatabase sqlConn;

DbCommand dbcmd;

SqlConn = new SqlDatabase ("your Connection string here");

dbcmd = sqlConn.GetStoredProcCommand(SaveEmployeeDetails);

dbcmd.CommandType = CommandType.StoredProcedure;

sqlConn.AddInParameter(dbcmd, "@EmployeeDt", SqlDbType.Structured, dt);

              

 sqlConn.ExecuteNonQuery (dbcmd);