SQL Server has introduced a functionality to pass a table data form into stored procedures and functions. This feature greatly simplifies the process of developing. The reason being, we need not worry about forming and parsing XML data. With the help of the table Input parameter to Stored Procedure we can save many round trips. Any SQL training will vouch for the fact that SQL is capable of accepting large, complex data in the form of parameters in a stored procedure.
Situation
Consider two tables
1) Sales Table
2) SalesDetails Table
dbo.Sales
dbo.SalesDetails
In the Sales Table various products are there for a specific Sales Id. The SalesDetails Table also displays costs of these products along with them.
SaleID=1, for instance, has PurchaseOrderNumber=’BigOrder’. It has three products- Product1,Product2 and Product3.
We should note that SalesId in Sales Table functions as the primary key and function of secondary key is performed by SalesId in SalesDetails Table.
Whenever there is a new sale, we want to impart a unique and distinctive number to the sale in the sales table. Also, we should provide the SalesDetails Table with the sold products in the product column.
Explanation
We have to generate two stored procedures to provide a solution to this situation. We use the first stored procedure for inserting the data in the dbo.Sales table. With the second stored procedure we store the data in dbo.SalesDetails table.
In case, we consider the situation displayed in the table above, we should use the first stored procedure only once for adding the data in the dbo.Sales table. To add the data in the dbo.SalesDetails table, we have to use the second stored procedure thrice. A total of four round trips occurs between the SQL server and application. The number of round trips can be reduced to one, if we use table Input parameter to Stored Procedure.
Step 1 – Open a new query window to the tempdb database
USE tempdb;
GO
GO
Step 2 – Create a Sales and SalesDetails table
CREATE TABLE dbo.Sales
( SaleID INT IDENTITY PRIMARY KEY,
CustomerID INT,
PurchaseOrderNumber VARCHAR(20)
( SaleID INT IDENTITY PRIMARY KEY,
CustomerID INT,
PurchaseOrderNumber VARCHAR(20)
);
CREATE TABLE dbo.SalesDetails( SalesDetailID INT IDENTITY,SaleID INT REFERENCES dbo.Sales(SaleID),Description VARCHAR(50),Price DECIMAL(18,2)
);GO
CREATE TABLE dbo.SalesDetails( SalesDetailID INT IDENTITY,SaleID INT REFERENCES dbo.Sales(SaleID),Description VARCHAR(50),Price DECIMAL(18,2)
);GO
Step 3 – Create traditional insert stored procedures for both tables
CREATE PROCEDURE dbo.SalesInsert
@CustomerID INT,
@PurchaseOrderNumber VARCHAR(20),
@SaleID INT OUTPUT
AS BEGIN
INSERT INTO dbo.Sales (CustomerID,PurchaseOrderNumber)
VALUES(@CustomerID,@PurchaseOrderNumber);
SELECT @SaleID = SCOPE_IDENTITY();
END;
GO
CREATE PROCEDURE dbo.SalesDetailInsert
@SaleID INT,
@Description VARCHAR(50),
@Price DECIMAL(18,2),
@SalesDetailID INT OUTPUT
AS BEGIN
INSERT INTO dbo.SalesDetails (SaleID,Description,Price)
VALUES(@SaleID,@Description,@Price);
SELECT @SalesDetailID = SCOPE_IDENTITY();
END;
GO
@CustomerID INT,
@PurchaseOrderNumber VARCHAR(20),
@SaleID INT OUTPUT
AS BEGIN
INSERT INTO dbo.Sales (CustomerID,PurchaseOrderNumber)
VALUES(@CustomerID,@PurchaseOrderNumber);
SELECT @SaleID = SCOPE_IDENTITY();
END;
GO
CREATE PROCEDURE dbo.SalesDetailInsert
@SaleID INT,
@Description VARCHAR(50),
@Price DECIMAL(18,2),
@SalesDetailID INT OUTPUT
AS BEGIN
INSERT INTO dbo.SalesDetails (SaleID,Description,Price)
VALUES(@SaleID,@Description,@Price);
SELECT @SalesDetailID = SCOPE_IDENTITY();
END;
GO
Step 4 – Show how we would have previously inserted an order
Here four round trips will occur in which we call dbo.SalesInsert stored procedure once to insert the data into dbo.Sales and dbo.SalesDetailInsert stored procedure thrice to insert all the products for a particular sales id
DECLARE @SaleID INT;
DECLARE @SalesDetailID INT;
BEGIN TRAN;
EXEC dbo.SalesInsert 12,'BigOrder',@SaleID OUTPUT;
EXEC dbo.SalesDetailInsert @SaleID,'Product 1',12.3,@SalesDetailID OUTPUT
EXEC dbo.SalesDetailInsert @SaleID,'Product 2',14.6,@SalesDetailID OUTPUT
EXEC dbo.SalesDetailInsert @SaleID,'Product 3',122.35,@SalesDetailID OUTPUT
COMMIT;
GO
SELECT * FROM dbo.Sales;
SELECT * FROM dbo.SalesDetails;
GO
DECLARE @SalesDetailID INT;
BEGIN TRAN;
EXEC dbo.SalesInsert 12,'BigOrder',@SaleID OUTPUT;
EXEC dbo.SalesDetailInsert @SaleID,'Product 1',12.3,@SalesDetailID OUTPUT
EXEC dbo.SalesDetailInsert @SaleID,'Product 2',14.6,@SalesDetailID OUTPUT
EXEC dbo.SalesDetailInsert @SaleID,'Product 3',122.35,@SalesDetailID OUTPUT
COMMIT;
GO
SELECT * FROM dbo.Sales;
SELECT * FROM dbo.SalesDetails;
GO
Now we create a table data type
Step 5 – Create a table data type to hold the sales details
CREATE TYPE dbo.SalesDetails AS TABLE
( Description VARCHAR(50),
Price DECIMAL(18,2)
);
GO
( Description VARCHAR(50),
Price DECIMAL(18,2)
);
GO
Step 6 – Modify the insert procedure to take detail lines as well
ALTER PROCEDURE dbo.SalesInsert
@CustomerID INT,
@PurchaseOrderNumber VARCHAR(20),
@SalesDetails dbo.SalesDetails READONLY,
@SaleID INT OUTPUT
AS BEGIN
BEGIN TRAN;
INSERT INTO dbo.Sales (CustomerID,PurchaseOrderNumber)
VALUES(@CustomerID,@PurchaseOrderNumber);
SELECT @SaleID = SCOPE_IDENTITY();
INSERT INTO dbo.SalesDetails (SaleID,Description,Price)
SELECT @SaleID, Description,Price
FROM @SalesDetails;
COMMIT;
END;
GO
@CustomerID INT,
@PurchaseOrderNumber VARCHAR(20),
@SalesDetails dbo.SalesDetails READONLY,
@SaleID INT OUTPUT
AS BEGIN
BEGIN TRAN;
INSERT INTO dbo.Sales (CustomerID,PurchaseOrderNumber)
VALUES(@CustomerID,@PurchaseOrderNumber);
SELECT @SaleID = SCOPE_IDENTITY();
INSERT INTO dbo.SalesDetails (SaleID,Description,Price)
SELECT @SaleID, Description,Price
FROM @SalesDetails;
COMMIT;
END;
GO
Step 7 – Perform an insert with a single round-trip
With the help of table data type to a stored procedure only one round trip is needed
DECLARE @SaleID INT;
DECLARE @SalesDetails dbo.SalesDetails;
INSERT INTO @SalesDetails VALUES('Product 1',12.3),('Product 2',14.66),('Product 3',122.35);
EXEC dbo.SalesInsert 12,'BigOrder',@SalesDetails,@SaleID OUTPUT;
GO
SELECT * FROM dbo.Sales;
SELECT * FROM dbo.SalesDetails;
GO
DECLARE @SalesDetails dbo.SalesDetails;
INSERT INTO @SalesDetails VALUES('Product 1',12.3),('Product 2',14.66),('Product 3',122.35);
EXEC dbo.SalesInsert 12,'BigOrder',@SalesDetails,@SaleID OUTPUT;
GO
SELECT * FROM dbo.Sales;
SELECT * FROM dbo.SalesDetails;
GO
Table input parameter in SQL is a massive march ahead where development and potential performance are concerned. It can lessen server round trips, utilize table constraints and widen the functionality of programming on the database engine.
passing datatable to stored procedure
In this post, I will show you, how DataTable from C# is passed to a stored procedure in Sql Server.
1. Create a TableType with the structure as same of the DataTable in Sql Server
2. Make this TableType as input parameter of stored procedure.
3. Finally, pass the DataTable to the TableType parameter of the stored procedure
Note : This is supported on Sql Server 2008 onwards.
Step 1: First Create a Table Employee
CREATE TABLE Employee(
EmpID VARCHAR(10)
, EmpName VARCHAR(50)
, Gender CHAR(1)
, DOJ DATETIME
)
Step 2 : Create a Table Type
CREATE TYPE EmpTableType AS TABLE (
EmpID VARCHAR(10)
, EmpName VARCHAR(50)
, Gender CHAR(1)
, DOJ DATETIME
)
Step 3 : Create Stored Procedure that will take Table Type that we created in Step 2 as parameter
CREATE PROCEDURE usp_GetEmpDetils(
@EmpDet EmpTableType READONLY
) AS
BEGIN
INSERT INTO Employee
SELECT * FROM @EmpDet
SELECT * FROM Employee
END
Step 4 : ADO.Net Code passing DataTable to the TableType Parameter in Stored Procedure
DataTable EmpTable = new DataTable();
EmpTable.Columns.Add("EmpID");
EmpTable.Columns.Add("EmpName");
EmpTable.Columns.Add("Gender");
EmpTable.Columns.Add("DOJ");
DataRow EmpRow = EmpTable.NewRow();
EmpRow["EmpID"] = "EMP0001";
EmpRow["EmpName"] = "Sandeep Mittal";
EmpRow["Gender"] = "M";
EmpRow["DOJ"] = "01/01/2010";
EmpTable.Rows.Add(EmpRow);
EmpTable.AcceptChanges();
SqlConnection connection = new SqlConnection("data source=ServerName;database=DBName;uid=UserID;pwd=Password");
SqlCommand selectCommand = new SqlCommand("usp_GetEmpDetils", connection);
selectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter tvpParam = selectCommand.Parameters.AddWithValue("@EmpDet", EmpTable);
tvpParam.SqlDbType = SqlDbType.Structured;
connection.Open();
grid.DataSource = selectCommand.ExecuteReader();
grid.DataBind();
connection.Close();
