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 – Stored Procedure
Consider two tables
1) Sales Table
2) SalesDetails Table
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.
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
Step 2 – Create a Sales and SalesDetails table
CREATE TABLE dbo.Sales ( 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
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
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
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
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
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
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. Thanks Vikas for your help in writing this article.
Reference: Pinal Dave (https://blog.sqlauthority.com)