SQL Server: Table-valued input parameters

Using table-valued parameters allows DBA/Developer to pass multiple rows of data to a stored procedure or function. This will minimize the number of round-trips to the server. To create a stored procedure that accepts a table-valued parameter as an input parameter, DBA/Developer first define a user-defined data type (UDT) using the CREATE TYPE...AS TABLE statement. Then, DBA/Developer can declare a variable of that type, initialize the variable by filling the table with values, and pass the variable as an input parameter to a stored procedure. The stored procedure can then use the table variable. For example, DBA/Developer could use the following Transact-SQL statement to define a user-defined table type named MyTableType:

CREATE TYPE MyTableType

AS TABLE
( DonorID int,Date datetime,Type char(10),Amount money);

After creating the UDT, DBA/Developer could create a procedure that accepts a parameter of that type using the following Transact-SQL:

CREATE PROCEDURE
dbo.usp_BuildLargeDonations
(@tableparm dbo.MyTableType READONLY)
AS BEGIN

-- Code to perform necessary actions to calculate values to insert

-- into LargeDonations using the table-valued input parameter

SELECT * FROM @tableparm

END
GO

Table-valued parameters in a stored procedure can only be input parameters, and cannot be modified within the stored procedure. Therefore, they must include the READONLY clause in the parameter list. The given CREATE PROCEDURE statement creates a stored procedure named usp_BuildLargeDonations, which accepts a table-valued parameter. After creating the stored procedure, DBA/Developer could declare and initialize a table variable to pass as the input parameter using a DECLARE statement, initialize the variable, and pass the variable to the usp_BuildLargeDonations stored procedure. For example, DBA/Developer could use the following Transact-SQL to declare and initialize a table variable and execute the usp_BuildLargeDonations stored procedure:

DECLARE @parm AS MyTableType;

INSERT INTO @parm
(DonorID, Date, Type, Amount)
SELECT DonorID, Date, Type, Amount
FROM DonorHistory
WHERE Amount > 10000;
EXEC usp_BuildLargeDonations @parm;

DBA/Developer should not create a Common Table Expression (CTE) that includes a SELECT...INTO and pass the CTE as an input parameter to a stored procedure because a CTE cannot contain an INTO clause and cannot be passed as a parameter to a stored procedure. A CTE can be used to make Transact-SQL code more readable when a query needs to reference the same result set multiple times. DBA/Developer can define a CTE by including the WITH clause with a query. The WITH clause specifies the expression name that will be used in the statement that immediately follows the CTE definition. The statement immediately following the CTE definition can use the expression one or more times, as if it were a table or view. The WITH clause must contain a column list identifying the available columns, unless all columns in the expression's query have distinct names. The syntax for creating a CTE is as follows:

WITH expression_name [(column_name [,...n])]
AS (CTE_query_definition)

After DBA/Developer create the CTE, the statement immediately following the CTE definition can reference the expression, but only the columns defined in the CTE expression are accessible. CTEs can be used as an alternative to using a view, temporary table, or subquery. DBA/Developer should not create a stored procedure that accepts multiple scalar-valued input parameters and performs a single INSERT. This would not offer the best performance because DBA/Developer would have to call the stored procedure multiple times to insert the rows. DBA/Developer should not create both a temporary table and a recursive stored procedure that accepts an array as a parameter and performs a single INSERT. There is no need to create a recursive stored procedure. A recursive stored procedure is a stored procedure that calls itself. Stored procedures calls can be nested up to 32 levels. In addition, DBA/Developer cannot pass an array as an input parameter to a stored procedure.