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.

CREATE TABLE [dbo].[Employee](
 [Emp_ID] [int] IDENTITY(1,1) NOT NULL,
 [Emp_name] [varchar](100) NULL,
 [Emp_Sal] [decimal](10, 2) NULL
) ON [PRIMARY]

GO

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

CREATE TYPE Employee AS TABLE
(
 [Emp_ID] [int] IDENTITY(1,1) NOT NULL,
 [Emp_name] [varchar](100) NULL,
 [Emp_Sal] [decimal](10, 2) NULL
)
GO

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.

CREATE PROCEDURE sp_InsertEmployee
 @employees employee READONLY

  AS
 INSERT INTO Employee(Emp_name,Emp_Sal)

 SELECT Emp_name,Emp_Sal
 FROM @employees

Where @employees is a table valued parameter passed to the stored procedure. Now we are done with creating a procedure, its time to check how it works. You can pass a datatable from C# or VB code as a parameter. What I will do here as a demonstration I will create a table variable and pass that table to the stored procedure.

DECLARE @testtable employee
  INSERT INTO @testtable(Emp_name,Emp_Sal)
 VALUES ('Anees', 1000.00),
('Rick', 1200.00),
('John', 1100.00)
select * from @testtable

Now we have our table ready. We need to pass it to stored procedure and see the result.

DECLARE @testtable employee
  INSERT INTO @testtable(Emp_name,Emp_Sal)
 VALUES ('Anees', 1000.00),
('Rick', 1200.00),
('John', 1100.00)

exec  sp_InsertEmployee @testtable
select * from Employee