TABLE VALUED PARAMETER IN SQL

A table-valued parameter (TVP) is a user-defined table type that represents a table structure in SQL Server.

While variables are used to pass values as parameters, TVPs can be used to pass a table as a parameter to a stored procedure or a function, which can be useful when you need to pass multiple rows of data to a stored procedure or a function.

To refer back on how to use variables as parameters, you can refer to this post how-to-use-variables-in-stored-procedures

Here’s a basic example of how to use a table-valued parameter in SQL Server

Step 1: Create a Table Type:

First, you need to create a user-defined table type.

In Microsoft SQL Server, a User-Defined Table Type (UDTT) is a personalized data type that mirrors the configuration of a table. It enables the definition of a table structure with designated columns and data types, which can subsequently be employed as a parameter in stored procedures or functions.

User-Defined Table Types prove beneficial for transmitting sets of data as parameters, particularly in situations where there’s a necessity to convey multiple rows of data to a stored procedure or function.

CREATE TYPE dbo.MyTableType AS TABLE
(
    Column1 INT,
    Column2 VARCHAR(50),
    -- Add more columns as needed
);

Step 2: Create a Stored Procedure that Takes TVP as a Parameter:

Now, you can create a stored procedure that takes the table-valued parameter as a parameter.

CREATE PROCEDURE dbo.MyStoredProcedure
    @MyTableParameter dbo.MyTableType READONLY
AS
BEGIN
    -- Your stored procedure logic here
    SELECT * FROM @MyTableParameter;
END;

The READONLY keyword indicates that the TVP is read-only within the stored procedure.

The use of READONLY helps enforce the idea that the stored procedure should not modify the contents of the table-valued parameter.

When a TVP is declared as READONLY, it prevents modifications to the parameter inside the stored procedure or function. This restriction is particularly useful when you want to ensure that the TVP is treated as an input-only parameter, and no modifications are made to its content during the execution of the stored procedure or function.

Step 3: Declare the TVP and execute the stored procedure

When calling the stored procedure, you can declare a variable of the table type and pass it to the procedure.

DECLARE @MyTableVariable dbo.MyTableType; -- This is a table variable to extract data from source table

INSERT INTO @MyTableVariable (Column1, Column2)
VALUES
    (1, 'Value1'),
    (2, 'Value2'),
    -- Add more rows as needed

EXEC dbo.MyStoredProcedure @MyTableParameter = @MyTableVariable;

In this example, the @MyTableVariable variable is of type dbo.MyTableType, and it’s populated with some sample data. The stored procedure is then executed with this table-valued parameter.

This allows you to pass a table of data to a stored procedure, which can be especially useful when dealing with multiple rows of data in a single call.

REAL TIME USE CASES OF TVPs

Table Valued Parameters (TVPs) becomes handy in many practical situations.

Here are some common use cases for Table-Valued Parameters:

Use Case 1: Bulk Inserts and Conditional Operations

• TVPs are often used for bulk inserts where you need to insert multiple rows of data into a table in a single operation.
• They can be more efficient than traditional row-by-row inserts, especially when dealing with large datasets.
• TVPs can be used also to enable conditional operations based on the content of the passed table. For instance, you can insert data into one table if it meets certain conditions and into another table if it meets different conditions.

Let us try out practically how TVPs can be used.

Our requirement is : Assume we have a large table with several rows. We need to copy data before a certain period of time into a another table with the condition that it has to be either laded completely or nothing at all.

Source:

I will again use the AdventureWorks2019 database for our practice. The table I use for this example is [AdventureWorks2019].[Purchasing].[PurchaseOrderDetail]. Based on our requirement, I want to copy Purchase orders of 2011 into another table using TVPs.

Target:

We need to create a table in which the Purchase orders of 2011 will be stored. In our target table, we will create only few required columns.

CREATE TABLE [AdventureWorks2019].[Purchasing].[PurchaseOrderDetail_2011]
  (   [PurchaseOrderID] INT
      ,[DueDate] DATE
      ,[OrderQty] SMALLINT
      ,[ProductID] INT
      ,[UnitPrice] MONEY
      ,[LineTotal] MONEY
      ,[ReceivedQty] DECIMAL
      ,[RejectedQty] DECIMAL
      ,[StockedQty] DECIMAL
  );

Let’s follow the generic steps which we read above.

Implementation Steps:

Step 1: Create user defined table data type

CREATE TYPE tblTypePO2011
AS TABLE 
(
      [PurchaseOrderID] INT
      ,[DueDate] DATE
      ,[OrderQty] SMALLINT
      ,[ProductID] INT
      ,[UnitPrice] MONEY
      ,[LineTotal] MONEY
      ,[ReceivedQty] DECIMAL
      ,[RejectedQty] DECIMAL
      ,[StockedQty] DECIMAL
);

Step 2: Create Procedure to accept table data as input and copy data to the target table

CREATE PROCEDURE copyPO2011Data (@TVP tblTypePO2011 READONLY)
AS
BEGIN			-- TO MARK START OF THE PROCEDURE CODE
BEGIN TRY       -- EXCEPTION HANDLING
BEGIN TRANSACTION --TO BEGIN THE TRANSACTION
INSERT INTO [AdventureWorks2019].[Purchasing].[PurchaseOrderDetail_2011] SELECT * FROM @TVP
COMMIT TRANSACTION
END TRY 

BEGIN CATCH 
PRINT  'ERROR DURING COPY TABLE OPERATION.'
ROLLBACK TRANSACTION
END CATCH 
END				-- TO MARK END OF THE PROCEDURE CODE

Step 3: Declare table variable, input 1st table data into this table variable and supply table variable as a parameter while executing the stored procedure.

 --Declare a temporary variable of Table Type
DECLARE @TABVAR tblTypePO2011
--Write your insert startement into this temproary variable
INSERT INTO @TABVAR
SELECT [PurchaseOrderID]
      ,CAST([DueDate] AS DATE) AS [DueDate] 
      ,[OrderQty]
      ,[ProductID]
      ,[UnitPrice]
      ,[LineTotal]
      ,[ReceivedQty]
      ,[RejectedQty]
      ,[StockedQty]
FROM [AdventureWorks2019].[Purchasing].[PurchaseOrderDetail]
WHERE CAST([DueDate] AS DATE) BETWEEN '2011-01-01' AND '2011-12-31' --Specify your condition
--Execute your procedure with Temporary variable 
EXEC copyPO2011Data @TABVAR

Note: If you observe here, it shows 2 times that certain number of rows are affected. This is because it first inserts into the temproary table we created, and then it inserts into the actual table when we write the EXEC function.

Step 4: Verify the results in target table

Now, let’s select our target table to see if the Purchasing order of 2011 is copied.

SELECT * FROM [AdventureWorks2019].[Purchasing].[PurchaseOrderDetail_2011];

Conclusion:-

• Now, in this use case, we have successfully copied the data from one table to another table using a simple procedure.
• We can further make this procedure more dynamic by passing the year, or due date as parameters and make this produre more reusable.
• Using Table Valued parameters becomes very useful in many real time situations.

Other use cases:-

1. Data Modification Operations:

• TVPs are useful for passing a set of data to perform update or delete operations based on certain criteria.
• This can reduce the number of round trips between the application and the database.

2. Dynamic Queries:

• TVPs enable the passing of dynamic data to stored procedures, allowing for more flexibility in constructing queries based on the application’s needs.

3. Data Validation:

• TVPs can be employed for passing multiple values that need to be validated against existing data in the database.
• This is useful when you want to check if a set of IDs or values exist in a reference table.

4. Hierarchical Data:

• When dealing with hierarchical data structures, TVPs can be used to pass entire hierarchies to stored procedures.
• This is common in scenarios involving organizational charts, product categories, or any other hierarchical data.

5. Report Parameters:

• TVPs can serve as parameters for reports, allowing the passing of sets of filter criteria for generating complex reports.
• This is useful when the report needs to be based on multiple selected values.

6. Custom Data Types:

• TVPs can be used to define custom data types that represent a specific structure, providing a clear and consistent way to pass data between the application and the database.

7. Filtering Data:

• TVPs are useful for passing multiple filter criteria to stored procedures or functions.
• This is common when you want to filter a result set based on multiple conditions, and the number of conditions may vary.

8. Dynamic IN Clauses:

• TVPs can be used to implement dynamic IN clauses in SQL queries, allowing you to pass a list of values for filtering.
• This is beneficial when the number of values in the IN clause is not known beforehand.

9. Multi-Table Operations:

• TVPs enable passing data that involves multiple related tables, allowing stored procedures to perform operations across these tables.
• This is useful for scenarios where you need to maintain consistency across multiple tables.

10. Parameterized Views:

• TVPs can be used in conjunction with views to create parameterized views that accept sets of data as input.
• This provides a way to filter and customize views based on dynamic criteria.

11. Integration with Application Code:

• TVPs provide a seamless way to integrate SQL Server with application code, especially in scenarios where the application code deals with sets of data.

12. Data Migration:

• TVPs are valuable for data migration scenarios where you need to transfer sets of data between tables or databases.

13. Data Comparison:

• TVPs can be used to pass two sets of data to a stored procedure for comparison purposes, allowing you to identify differences between them.

14. Audit Trail:

• TVPs can be employed to pass audit trail data, recording changes made by the application or users for later analysis.

15. Data Warehousing:

• In data warehousing scenarios, where you’re dealing with large sets of data, TVPs can be used to facilitate ETL (Extract, Transform, Load) operations efficiently.

16. Parameterized Aggregations:

• TVPs can be utilized for parameterized aggregations, allowing you to perform operations like sum, average, or count on a set of values passed as a parameter.

17. Temporal Queries:

• TVPs can be used in temporal table scenarios to pass sets of data representing historical changes, allowing for historical data analysis.