Send DataTable as parameter through Stored Procedure in MSSQL

In this post, we want to discuss sending a DataTable as a parameter in SQL Server. Before we get started, if you want to know about SQL Rowversion, please go through the following article: What is rowversion in SQL Server?

You can add, modify, and delete operations on a set of records in the database using a simple form. The details also need to be shown after adding, updating, and deleting records in a table. And the data table should be given to a stored procedure to perform the operations. This feature will work with SQL Server 2008.

First, create a table type with matching columns that comes from the front-end. In this case:

/* Creating type for the procedure parameter*/
CREATE TYPE EmpType AS TABLE
(
    ID INT,
    Name VARCHAR(3000),
    Address VARCHAR(8000),
    Operation SMALLINT
)

Write the procedure for the operations.

CREATE PROCEDURE spEmpDetails
   @Details EmpType READONLY
AS
BEGIN
   –For deleting the details from the table
      DELETE FROM EmployeeDetails 
      WHERE ID IN(SELECT ID FROM @Details WHERE Operation=2)

   –For updating the details in the table
      UPDATE E SET E.Name=D.Name, E.Address=D.Address FROM EmployeeDetails E, @Details D
      WHERE D.ID=E.ID and D.Operation=1

   –For inserting the new records in the table
      INSERT INTO EmployeeDetails(ID, Name, Address)
      SELECT ID, Name, Address FROM @Details WHERE Operation=0;
END
GO

send datatable as parameter in SQL Server