Pass a Table to a Stored Procedure With a Table Type

To pass a table to a stored procedure, you create a table type and declare a parameter of that type. The procedure receives every row in one call, so you don’t run it once per row. SQL Server calls this a table-valued parameter, and the parameter must be READONLY.

The Problem

A stored procedure takes one parameter easily and two without trouble. A thousand parameters would be unwieldy, and a thousand calls would be slow. A table-valued parameter solves this. You fill a table once and pass it as a single parameter. The procedure does all the work in one pass.

The Sample Table

Classes holds the approved classes of a small training center. A list of new classes arrives, and all of it should go into Classes with one call. Run this in any test database. Every result below comes from SQL Server 2025.

CREATE TABLE Classes (ClassName VARCHAR(50) NOT NULL, Hours INT NULL, Status VARCHAR(10) NOT NULL DEFAULT 'Approved');
GO

INSERT INTO Classes (ClassName, Hours) VALUES ('Tea Tasting Basics', 3), ('Garden Planning', 4);
GO

Step One: Create a Table Type

A table type is a data type that describes the shape of a table. Like INT or VARCHAR, it stores no data. It only defines the columns, so the new type has the same two columns as the list.

CREATE TYPE ClassListType AS TABLE (ClassName VARCHAR(50) NOT NULL, Hours INT NULL);

Step Two: Write the Procedure

Declare the parameter with the table type, and add READONLY after it. Inside the procedure, read the parameter like any other table.

CREATE PROCEDURE AddClasses @NewClasses ClassListType READONLY
AS
INSERT INTO Classes (ClassName, Hours)
SELECT ClassName, Hours FROM @NewClasses;

Step Three: Fill a Variable and Call It

Declare a variable of the table type, put rows in it, and pass it to the procedure. Keep the whole script in one batch, because the variable lives only until the batch ends. The final query shows the result: the two original classes and the three new ones.

DECLARE @List AS ClassListType;
INSERT INTO @List (ClassName, Hours) VALUES ('Kite Making', 2), ('Bread Baking', 5), ('Map Reading', 3);
EXEC AddClasses @NewClasses = @List;
SELECT ClassName, Hours, Status FROM Classes ORDER BY ClassName;

One Call, Many Rows

The same procedure takes a thousand rows as easily as three. This script builds a list of 1,000 workshops and passes it in. Classes ends with 1,005 rows, the five from before and the new 1,000.

DECLARE @Big AS ClassListType;
INSERT INTO @Big (ClassName, Hours)
SELECT TOP (1000) CONCAT('Workshop ', ROW_NUMBER() OVER (ORDER BY (SELECT NULL))), 2
FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;
EXEC AddClasses @NewClasses = @Big;
SELECT COUNT(*) AS ClassesNow FROM Classes;

Four Common Errors

Each message points to one rule. The first one appears when you query the variable in a separate batch. SQL Server says, Msg 1087: Must declare the table variable @List.

SELECT ClassName, Hours FROM @List;

The second one appears when the parameter has no READONLY. The message is Msg 352: the table-valued parameter must be declared with the READONLY option.

CREATE PROCEDURE AddClassesNoReadOnly @NewClasses ClassListType
AS
SELECT ClassName FROM @NewClasses;

The third one appears when a procedure tries to change the parameter. Msg 10700 says the parameter is READONLY and cannot be modified.

CREATE PROCEDURE EditClasses @NewClasses ClassListType READONLY
AS
DELETE FROM @NewClasses;

The fourth one appears when you put READONLY on an ordinary type. Msg 346 says the parameter can’t be READONLY because it isn’t a table-valued parameter.

CREATE PROCEDURE WrongParameter @Count INT READONLY
AS
SELECT @Count;

Changing the Rows Inside

A reader asked whether a procedure can change a column of the table it receives. It can’t change the parameter. It can copy the rows into its own variable of the same type and change the copy. A SELECT returns the result. This procedure adds one hour to each class.

CREATE PROCEDURE ChangeCopy @NewClasses ClassListType READONLY
AS
BEGIN
  DECLARE @Work AS ClassListType;
  INSERT INTO @Work (ClassName, Hours) SELECT ClassName, Hours FROM @NewClasses;
  UPDATE @Work SET Hours = Hours + 1;
  SELECT ClassName, Hours FROM @Work ORDER BY ClassName;
END;

DECLARE @Two AS ClassListType;
INSERT INTO @Two (ClassName, Hours) VALUES ('Pottery', 2), ('Chess', 1);
EXEC ChangeCopy @NewClasses = @Two;

It returns Chess with 2 hours and Pottery with 3, one more than each input.

Find the Table Type

A table type shows up in the catalog view sys.table_types. Join it to sys.columns to list its columns. In Object Explorer, look under Programmability, Types, User-Defined Table Types.

SELECT t.name AS TypeName, c.name AS ColumnName, TYPE_NAME(c.user_type_id) AS DataType
FROM sys.table_types AS t
JOIN sys.columns AS c ON c.object_id = t.type_table_object_id
ORDER BY c.column_id;

Clean Up

Remove the procedures, the type and the table when you finish.

DROP PROCEDURE ChangeCopy;
DROP PROCEDURE AddClasses;
DROP TYPE ClassListType;
DROP TABLE Classes;

What to Remember

Create a table type, declare the parameter with it, and add READONLY. Fill a variable of that type, then pass it in one call. If the procedure must change the rows, copy them into a variable first.

To go further, read Table-Valued Parameters: Sending Thousands of Rows in One Call and SQL SERVER – 2008 – Introduction to Table-Valued Parameters with Example.

A table parameter is not a bulk loader, it is a table you hand over to read.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.