Passing A Table To A Stored Procedure In SQL Server Using Table Valued Parameters

Many times we come across a situation where we need to pass a table to stored procedure from C# code. In such scenarios what we can do is either loop through table and send rows one by one or we can directly pass the full table to the procedure. Passing rows one by one may be inefficient as we have to iterate through rows and call procedure again and again.

SQL Server provides us an efficient way of doing the same using 'User Defined Types' So for passing a table valued parameter to a stored procedure we need to create a user defined table type that will have same columns that we want to pass to the table. Click Database Node > Programmability > Types, then User-Defined Table Types. Now we create a table which will be filled by stored procedure.

Once the table is being created we need to create a type same as that of a table.

Now we are done with creating a type and a table we need to create a stored procedure that will accept a type and insert into the table using the type.