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.<\/p>\n\n\n\n
This previous tutorial discussed output parameters in SQL procedures<\/a>, 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.<\/p>\n\n\n\n First, let’s create a sample table to store Customers<\/em>:<\/p>\n\n\n\n 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:<\/p>\n\n\n\n Now, let’s create a stored procedure that accepts table-valued parameter<\/a>, using the table TYPE named CustomerTableType<\/em> , defined in the paragraph above. In this particular use case, the stored procedure with a very explicit name performs these actions: <\/p>\n\n\n\n To use this stored procedure, follow these 3 steps: <\/p>\n\n\n\n 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. <\/p>\n\n\n\n You can extend this concept with even a larger scope of flexibly by passing XML tables to stored procedures<\/a>. Then the variable is completely independent of the data structure. <\/p>\n\n\n\n For more examples about usage of stored procedures like modification, visit our SQL Server stored procedure modification guide<\/a>. And also this similar tutorial, but this time using functions instead of procedures.<\/p>\n\n\n\nSet Up the Customers Table<\/h4>\n\n\n\n
-- Create the customers table with 3 sample columns\nCREATE TABLE [dbo].[CUSTOMERS] (\n [CustomerNumber] int IDENTITY(1,1),\n [Name] nvarchar(20) UNIQUE,\n [City] nvarchar(20)\n)\nGO\n<\/pre>\n\n\n\n
Step 1: Create a Type for the Table-Valued Parameter<\/h2>\n\n\n\n
-- Create a type that represents our table structure\nCREATE TYPE CustomerTableType AS TABLE (\n [Name] nvarchar(20),\n [City] nvarchar(20)\n)\nGO\n<\/pre>\n\n\n\n
Step 2: Create a Stored Procedure with a Table-Valued Parameter Input<\/h2>\n\n\n\n
\n
-- Create a stored procedure that takes a table-valued parameter\nCREATE PROCEDURE dbo.uspAddMultipleCustomers\n @Customers CustomerTableType READONLY\nAS\n INSERT INTO dbo.CUSTOMERS ([Name], [City])\n SELECT [Name], [City] FROM @Customers\nGO\n<\/pre>\n\n\n\n
Step 3: Use the SQL Server Stored Procedure<\/h2>\n\n\n\n
\n
-- Declare a variable of our table type\nDECLARE @NewCustomers CustomerTableType\n\n-- Insert data into the table-valued variable\nINSERT INTO @NewCustomers ([Name], [City])\nVALUES \n ('Alice', 'New York'), -- North America\n ('Jorge', 'Buenos Aires'), -- South America\n ('Chen', 'Beijing'), -- Asia\n ('Fatima', 'Cairo'), -- Africa\n ('Anya', 'Moscow'); -- Europe\n\n-- Execute the stored procedure with the table-valued parameter\nEXEC dbo.uspAddMultipleCustomers @NewCustomers\n<\/pre>\n\n\n\nUnlike XML, table typed valued parameters allows flexibility<\/h3>\n\n\n\n