Use a table parameter in a Stored Procedure in SQL Server

In this T-SQL guide, we’ll explore how to use table-valued parameters in a SQL Server stored procedure. It enables the passing of a table as a parameter and mot only its values like by using a select statement for example. Thus, this method is particularly useful for scenarios where you need to pass multiple rows of data to a stored procedure without having to pass too many variables.

Differences Between OUTPUT Parameters and Table-Valued Parameters

This previous tutorial discussed output parameters in SQL procedures, which return single values or single line values if you extend the concept. Table-valued parameters, on the other hand, allow you to pass an entire set of data, like a table, to a stored procedure.

Set Up the Customers Table

First, let’s create a sample table to store Customers:

-- Create the customers table with 3 sample columns
CREATE TABLE [dbo].[CUSTOMERS] (
   [CustomerNumber] int IDENTITY(1,1),
   [Name]           nvarchar(20) UNIQUE,
   [City]           nvarchar(20)
)
GO

Step 1: Create a Type for the Table-Valued Parameter

Before we create the stored procedure, we need to define a type that represents the structure of the table we want to pass as a parameter:

-- Create a type that represents our table structure
CREATE TYPE CustomerTableType AS TABLE (
    [Name] nvarchar(20),
    [City] nvarchar(20)
)
GO

Step 2: Create a Stored Procedure with a Table-Valued Parameter Input

Now, let’s create a stored procedure that accepts table-valued parameter, using the table TYPE named CustomerTableType , defined in the paragraph above. In this particular use case, the stored procedure with a very explicit name performs these actions:

Receive the Table as an input table parameter (variable).
Insert all of its content into the classical Customers table.

-- Create a stored procedure that takes a table-valued parameter
CREATE PROCEDURE dbo.uspAddMultipleCustomers
    @Customers CustomerTableType READONLY
AS
    INSERT INTO dbo.CUSTOMERS ([Name], [City])
    SELECT [Name], [City] FROM @Customers
GO

Step 3: Use the SQL Server Stored Procedure

To use this stored procedure, follow these 3 steps:

First declare a variable of our table type.
Populate it with one or multiple lines of data here we insert 5 lines.
And then pass it to the procedure.

-- Declare a variable of our table type
DECLARE @NewCustomers CustomerTableType

-- Insert data into the table-valued variable
INSERT INTO @NewCustomers ([Name], [City])
VALUES 
    ('Alice', 'New York'),      -- North America
    ('Jorge', 'Buenos Aires'),  -- South America
    ('Chen', 'Beijing'),        -- Asia
    ('Fatima', 'Cairo'),        -- Africa
    ('Anya', 'Moscow');         -- Europe

-- Execute the stored procedure with the table-valued parameter
EXEC dbo.uspAddMultipleCustomers @NewCustomers

Unlike XML, table typed valued parameters allows flexibility

To conclude, table-valued parameters offer a flexible way to pass complex data structures like tables to stored procedures in SQL Server. This method is highly efficient for bulk data operations and complex business logic implementations.

You can extend this concept with even a larger scope of flexibly by passing XML tables to stored procedures. Then the variable is completely independent of the data structure.

For more examples about usage of stored procedures like modification, visit our SQL Server stored procedure modification guide. And also this similar tutorial, but this time using functions instead of procedures.