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.