Table Valued Parameters in SQL Server Stored Procedure

Using table valued parameters in SQL stored procedure, SQL developers can provide a solution to pass a list of parameter values to the SQL stored procedure. Passing multiple values is now possible with table-valued parameters in SQL Server stored procedure programming.

Before SQL Server table-valued parameters were introduced, Transact-SQL developers were passing parameter value list as a concatenated string of values stored in a varchar() or nvarchar() variable. Character variable and SQL statement were concatenated to build a dynamic SQL statement. The final dynamic SQL statement created by string concatenation process was executed by calling the EXEC sp_execute command.

Of course dealing with dynamic SQL statements is not a secure way of SQL programming. After SQL Server introduced table valued parameters, it is now more convenient to use table-valued parameters while passing values list to SQL stored procedure.

Let's make an SQL example showing how to pass multiple values as a parameter to an SQL stored procedure. Assume that we want to pass a list of ID values to a sample SQL stored procedure which returns relational data for input ID list values.

In this SQL tutorial, the below sample stored procedure and sample user defined table type can be created on SQL Server 2012 AdventureWorks sample database. Developers can execute the given scripts in this tutorial on sample AdventureWorks database.

The first step is to create table type. to create a user defined table type, CREATE TYPE command can be used in a syntax shown as below. I chosed to create table type named tabletype_ProductIdList which stores int (integer) values in table column named ProductID.


CREATE TYPE tabletype_ProductIdList AS TABLE (

 ProductID int NOT NULL PRIMARY KEY

)

Code

I suggest SQL programmers and database administrators to create user-defined table type for generic use of integer values list for passing integer values to SQL stored procedures and SQL functions. I'm sure that SQL developers will use this user-defined table type for passing ID column values to stored procedures in many cases during development process.

After you create table type, you will be able to display it in Object Explorer windows in SQL Server Management Studio as seen in below screenshot.

user-defined table types in SQL Server Management Studio

Also SQL Server database administrators and SQL developers can query table types from sys.table_types system view.


SELECT * FROM sys.table_types

Code

You can check if a user defined table type is created on a database, to get an idea whether SQL developers and administrators are really following the T-SQL programming improvements or not. I believe in every database a table type to pass multiple integer values to SQL stored procedure.

If a table type will not be used any more, DBAs can remove table type by executing below DROP TYPE command.


DROP TYPE tabletype_ProductIdList

Code

Now, SQL developers are ready to create sample stored procedure which accepts multiple parameter values. We will use a parameter of table type so that the input parameter can store multiple values as rows and pass these multiple values to the SQL Server stored procedure.

Within the stored procedures codes, developers can join table-type parameter just like a table to other database tables. You can see below sample SQL codes includes SELECT query from table-valued parameter and an LEFT OUTER JOIN to a database table.


CREATE PROCEDURE sp_ListProductSalesQuantity (

 @ProductIdList tabletype_ProductIdList READONLY

)

AS



SELECT

 p.ProductID, p.Name, q.OrderQuantity

FROM (

 SELECT p.ProductID, SUM(ISNULL(d.OrderQty,0)) OrderQuantity

 FROM @ProductIdList p

 LEFT OUTER JOIN Sales.SalesOrderDetail d ON d.ProductID = p.ProductID

 GROUP BY p.ProductID

) q

INNER JOIN Production.Product p ON p.ProductID = q.ProductID

ORDER BY p.ProductID



GO

Code

While you create stored procedure which includes table valued parameters or table-type parameters, it is important to define input parameter as READONLY. Otherwise, SQL Engine will throw the following error message:

Msg 352, Level 15, State 1, Procedure sp_ListProductSalesQuantity, Line 2
The table-valued parameter "@ProductIdList" must be declared with the READONLY option.

This means you can not use table-type variables defined as a parameter to SQL stored procedure to return data from SQL procedure. Table valued parameters can only be defined as readonly parameters which cannot be updated within SQL stored procedure.

After we create user-defined table-type and sample SQL Server stored procedure which accepts input parameter in that table-type, we can create SQL script which will populate input parameter with data and call stored procedure. Here is example SQL codes to execute SQL Server stored procedure which will summarize all steps described in this SQL tutorial.


DECLARE @RandomProducts tabletype_ProductIdList



INSERT INTO @RandomProducts (ProductID)

SELECT TOP 10 ProductID FROM Production.Product ORDER BY NEWID()



EXEC sp_ListProductSalesQuantity @RandomProducts

Code

Above SQL codes first declare table-type variable. As a second step, table-type variable is populated with random product ids. In the last step, SQL stored procedure is executed. The table-type variable which include multiple integer ID list is passed to stored procedure as table-valued parameter.

SQL Server stored procedure returns product name and sales quantities for product Id's passed in table-valued parameter. Here is sample return set from stored procedure when executed on SQL Server 2012 sample database AdventureWorks.

table-valued parameter to pass multiple values to SQL stored procedure

I believe, developers and database administrators will love to use user-defined table type and table valued parameters in their SQL tasks.