In this post, we want to discuss send 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
Write the procedure for the operations.
CREATE PROCEDURE spEmpDetails
@Details EmpType READONLY
–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;