- Working with Multiple Groupings of Data in a Single Command
- Passing Multiple Data Rows to a Stored Procedure with Table-Valued Parameters
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Passing Multiple Data Rows to a Stored Procedure with Table-Valued Parameters
Table-valued parameters allow for passing array-like structures to a stored procedure. Front-end or middle-tier applications often have to pass multiple rows of data to a stored procedure, which in turn should populate multiple data tables. Before table-valued parameters were introduced, we had to attempt to construct necessary rows within the stored procedure based on numerous scalar parameters. Another option was to have multiple stored procedures, each updating a single table, but this approach required multiple database calls. A better alternative became available starting with SQL Server 2000, when XML support was introduced. Using XML support, we could pass a large XML string to a stored procedure as a single parameter, which we could subsequently parse and translate into rows, updating data in multiple tables. However, decomposing large XML strings can be time- and resource-intensive. Using the new table-valued parameters in SQL Server 2008, we can easily pass multiple data rows to a stored procedure in native format—without having to parse XML strings.
To use table-valued parameters, you must first create a user-defined type that specifies the structure of the table you'll use later in stored procedures. For example, the following statement creates a table data type with a key column and name column:
CREATE TYPE my_table_type AS TABLE (
key_column INT,
name_column VARCHAR(20)
)
Next, I'll create a stored procedure that accepts the parameter, using my_table_type. Notice that I must use the READONLY keyword to reference the variable of my_table_type.
CREATE PROCEDURE table_parameter_sample_proc (
@datasource my_table_type READONLY)
AS
SELECT
key_column,
name_column
FROM @datasource
GO
Now I can declare and populate the variable, using the data type defined earlier, and call the stored procedure:
DECLARE @datasource AS my_table_type
INSERT @datasource (key_column, name_column)
VALUES
(1, 'Abby'),
(2, 'Becky'),
(3, 'Chloe')
EXEC table_parameter_sample_proc @datasource
We cannot update the data in the table-valued parameters—it can only be read. However, we can easily work around this limitation by copying the data into a temporary table or another table variable (not using the user-defined table data type) within the stored procedure, if updating data is necessary.
The type created earlier is available to all connections and users within the database until I explicitly drop it by using the DROP TYPE statement. Note that you cannot set a table-valued parameter to NULL, but you can pass an empty table to the stored procedure.
Summary
SQL Server 2008 has empowered the Transact-SQL language with several important additions and enhancements. Using the new features discussed in this series, you can write cleaner, more elegant code; your software is likely to perform better; and you could save a significant amount of storage.