In this post, we want to discuss about send datatable as 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 of 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 which 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;