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.

Consider a situation where we have two tables: Sales Table and SalesDetails Table. The Sales Table contains various products for a specific Sales Id, while the SalesDetails Table displays the costs of these products. The SalesId in the Sales Table functions as the primary key, and the SalesId in the SalesDetails Table performs the function of a secondary key.

Traditionally, to add data to these tables, we would need to make multiple round trips between the SQL server and the application. However, with the introduction of table input parameters in SQL Server, we can reduce the number of round trips to just one.

Step 1: Create the Sales and SalesDetails tables

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)
);

Step 2: 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;

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;

Step 3: Insert data using traditional stored procedures

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;

Step 4: Introducing table input parameter

CREATE TYPE dbo.SalesDetails AS TABLE
( 
  Description VARCHAR(50),
  Price DECIMAL(18,2)
);

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;

Step 5: Insert data using table input parameter

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;

By utilizing the table input parameter in SQL Server, we can significantly reduce the number of round trips between the SQL server and the application. This not only improves performance but also allows for more efficient programming on the database engine.

Table input parameter in SQL Server is a massive step forward in terms of development and potential performance. It can lessen server round trips, utilize table constraints, and widen the functionality of programming on the database engine.

Thank you for reading!