You can pass any type of data as parameters to a stored procedure. A stored procedure is a SQL code that you can execute over and over again. The stored procedure can act based on the parameter value(s) that is passed.
It's easy to pass integer or string type of data but the question is "how can we pass a list of parameters to a stored procedure?". So the answer is "Table-Valued Parameters aka TVPs".
A table-valued parameter is a parameter with a table type. Using this parameter, you can send multiple rows of data to a stored procedure or a parameterized SQL command in the form of a table. Transact-SQL can be used to access the column values of the table-valued parameters. Table-valued parameters are declared by using user-defined table types. You can use table-valued parameters to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.
Passing Table-Valued Parameters to a stored procedure in the following steps:
- Create a User-defined table type that corresponds to the table that you want to populate.
- Pass the User-defined table to the stored procedure as a parameter
- Inside the stored procedure, select the data from the passed parameter and insert it into the table that you want to populate.
1. User-defined table types
User-defined table types are the predefined tables that the schema definition is created by the users and helps to store temporary data. User-defined table types support primary keys, unique constraints and default values, etc. Here is an example :
CREATE TYPE ChapterType AS TABLE (
Id INT,
Name VARCHAR(128)
)
When we execute the above query, we can see the created user-defined table type under the User-Defined Table Types folder in SQL Server Management Studio (SSMS).
2. Using Table-Valued Parameters in Stored Procedures
Table-Valued Parameters reference their types from the user-defined table so they inherit the schema of the table. The multiple parameter values can pass as an input to the stored and then they can be handled by the stored procedure.
In this first example, we will insert multiple rows into a table with the help of Table-Valued Parameters. Firstly, we will create a table whose name is Lesson.
CREATE TABLE Chapters (
Id INT PRIMARY KEY,
Name VARCHAR(50)
)
Now let’s create a stored procedure that accepts the ChapterType variable as a parameter. Inside the stored procedure, we will SELECT all the records from this variable and insert them into the Chapter table. Here is an example:
CREATE PROCEDURE uspAddChapters
@ChapterList ChapterType READONLY
AS
BEGIN
INSERT INTO Chapters
SELECT * FROM @ChapterList
END
Note: The Table-Valued Parameter must be declared as read-only. The reason for this usage method is that we cannot make any manipulation (INSERT, UPDATE, DELETE) on the TVP in the routine body.
3. Executing Stored Procedure with Table-Valued Parameters
The final step is to create a variable of the ChapterType, fill it with some data and pass it to the stored procedure. Here is an example:
DECLARE @ChapterType ChapterType
INSERT INTO @ChapterType VALUES (1, 'Chapter One')
INSERT INTO @ChapterType VALUES (2, 'Chapter Two')
INSERT INTO @ChapterType VALUES (3, 'Chapter Three')
INSERT INTO @ChapterType VALUES (4, 'Chapter Four')
EXECUTE uspAddChapters @ChapterType
Then the table variable is passed as a parameter to the Stored Procedure and the Stored Procedure is executed which finally inserts the records into the Chapters Table.