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

Solarwinds

SQL SERVER - Significance of Table Input Parameter to Stored Procedure ks1

dbo.SalesDetails

SQL SERVER - Significance of Table Input Parameter to Stored Procedure ks2

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)

Solarwinds
, , ,
Previous Post
SQL SERVER – Contest to Win Amazon Card – Experience Cloud Data Management System (CDMS) and NuoDB
Next Post
SQL SERVER – Difference Between NOLOCK and NOWAIT Hints

Related Posts

9 Comments. Leave new

  • Can I use table variable in place of table data type?

    Reply
  • Sandeep Mittal
    January 31, 2013 9:42 am

    Hi Rajnish

    Passing of table variable directly to stored procedure is not allowed. It can only be done using TableType.
    Also, you may refer the below link to see how datatable from c# is passed to stored procedure

    Reply
  • thank you very much for this wonderful post

    Reply
  • Yes, This is really usefull feature in SQL Server, but this can be used one and only if the schema of the Table Type is not going to change anymore. If it is going to change then this will be an burdon from the deployment and maintenance perpective. Because we do not have Alter option for the Table type. We have to drop all the Stored procedures and Function which refers this Table Type and again recreate the Table type and create the SP’s and functions.

    Reply
  • Hey Sandeep / Pinal,

    Can we pass a table to a stored procedure and modify it within the procedure? I want to create StoredProc(table a), which takes a table as input and adds a row to the table. Then I want to call the procedure with TableA, TableB as parameters. I know we can do this using dynamic SQL (EXEC command). I am trying to see if there is a feature in SQL that is similar to generics in c# or templates in C++.

    Reply
  • When it is better to pass xml or table variable in store procedure & why Please reply.

    Reply
  • Hi,
    ALTER PROCEDURE dbo.SalesInsert is throwing an error during compilation. It is because of the @SaleID variable which is being selected from @SalesDetails. Could you please rectify it.

    Thanks

    Reply

Leave a Reply

Menu