SQL SERVER – Significance of Table Input Parameter to Stored Procedure

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

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

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)