Table-valued parameters allow multiple rows of data to be passed to a stored procedure by some Transact-SQL code or from front-end application. The maximum size that a table-valued parameter can have is equal to the maximum memory size of the database server.

Passing table-valued parameters to a stored procedure is a three-step process:

  1. Create a user-defined table type that corresponds to the table that you want to populate.
  2. Pass the user-defined table to the stored procedure as a parameter
  3. Inside the stored procedure, select the data from the passed parameter and insert it into the table that you want to populate.

    Let’s take a look at an example of how we can pass a data table to a stored procedure using table valued function.

    First, create a table that we want to populate. Execute the following script:

    CREATE DATABASE ShowRoom


USE ShowRoom
Create Table Cars
(
    Id int primary key,
    Name nvarchar(50),
    company nvarchar(50)
)
Go

    In the script above, we create a database called ShowRoom with one table i.e. Cars. The Cars table has three columns: Id, Name, and company. We will use a stored procedure to populate the Cars table.

    As described earlier, the first step is to create a user-defined table type that corresponds to the table that you want to populate. Execute the following script to do so:

    CREATE TYPE CarTableType AS TABLE
(
     Id int primary key,
    Name nvarchar(50),
    company nvarchar(50)
)
Go

    In the script above, we create the  CarTableType user-defined variable of the Table type. This is the variable that we will pass to the stored procedure. It can be seen that the columns of the CarTableType variable are similar to those in the Cars table.

    Now let’s create a stored procedure that accepts the CarTableType variable as a parameter. Inside the stored procedure, we will SELECT all the records from this variable and insert them into the Cars table. Execute the following script to create such stored procedure:

    CREATE PROCEDURE spInsertCars
@CarType CarTableType READONLY
AS
BEGIN
    INSERT INTO Cars
    SELECT * FROM @CarType
END

    In the above script, we create the spInsertCars stored procedure. It is important to mention that you have to specify the user-defined parameter as READONLY inside the stored procedure, failing to do so results in a runtime error.

    You can see that the spInsertCars stored procedure accepts the CarTableType parameter and assigns it to the @CarType variable of the CarTableType type.

    The final step is to create a variable of the CarTableType variable, populate it with dummy data and pass it to the spInsertCars stored procedure. Take a look at the following script:

    DECLARE @CarTableType CarTableType

INSERT INTO @CarTableType VALUES (1, 'Corrolla', 'Toyota')
INSERT INTO @CarTableType VALUES (2, 'Civic', 'Honda')
INSERT INTO @CarTableType VALUES (3, '6', 'Audi')
INSERT INTO @CarTableType VALUES (4, 'c100', 'Mercedez')
INSERT INTO @CarTableType VALUES (5, 'Mustang', 'Ford')

EXECUTE spInsertCars @CarTableType

    In the script above, we first declare the @CarTableType variable of type CarTableType. We then insert 5 dummy records into this variable. Finally, we execute the spInsertCars stored procedure and pass it the @CarTableType variable as a parameter.

    Inside the stored procedure, five records from the @CarTableType variable are selected and inserted into Cars table. Now if you select all the records from the Cars table, you should see the newly inserted records. Execute the following script to do so:

    SELECT * FROM Cars

    The output of the script above looks like this:

    It can be seen from the output that all the records from the @CarTableType variable have been inserted into the Cars table.


    Ref:
    https://codingsight.com/passing-data-table-as-parameter-to-stored-procedures/#:~:text=Create%20a%20user%2Ddefined%20table,that%20you%20want%20to%20populate.