Passing Data table as Parameter to Stored Procedures

Real-world database applications need to make multiple requests from the front end to the database in order to carry out all sorts of functions.

If an application is data-intensive, such as the ones used in banks or airports etc, the number of data trips can be huge. Each request to a database utilizes bandwidth and requires time to execute. Without the table-valued parameters, a front application needs to make multiple data trips in order to manipulate multiple rows of data. However, with table-valued parameters, multiple rows can be inserted, updated and deleted from a database using a single parameterized command that takes a table-valued parameter.

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.

In this article, we will study how we can pass a data table to a stored procedure. However, before that let’s see how tabular data used to be passed before table-valued parameters.

Passing Data in Tabular Form before Table Valued Parameters

Table-valued parameters were introduced in SQL Server 2008. Before that, there were limited options to pass tabular data to stored procedures. Most developers used one of the following methods:

1. Data in multiple columns and rows was represented in the form of a series of parameters. However, the maximum number of parameters that can be passed to a SQL Server stored procedure is 2,100. Therefore, in the case of a large table, this method could not be used. Furthermore preprocessing is required on the server side in order to format the individual parameters into a tabular form.
2. Create multiple SQL statements that can affect multiple rows, such as UPDATE. The statements can be sent to the server individually or in the batched form. Even if they are sent in the batched form, the statements are executed individually on the server.
3. Another way is to use delimited strings or XML documents to bundle data from multiple rows and columns and then pass these text values to parameterized SQL statements or stored procedures. The drawback of this approach was that you needed to validate the data structure in order to unbundle the values.

Passing Data table as Parameter to Stored Procedures

Now let’s see how table-valued parameters can be used to send data to a stored procedure without facing any of the issues discussed in the previous section. 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.

In this section, we will use table-valued parameters along with a stored procedure to insert multiple rows to a data table.

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.