What's new in SQL Server 2008? Table-value parameters
Otra de las novedades de T-SQL son parámetros "Table-value".
Este tipo de parámetros permite enviar múltiples filas de datos a una sentencias T-SQL, a un procedimiento almacenado o a una función, sin utilizar tablas temporales ni múltiples parámetros.
Este tipo de parámetros ofrecen un mejor rendimiento y flexibilidad que las tablas temporales para pasar una lista de parámetros.
Beneficios
- Do not acquire locks for the initial population of data from a client.
- Do not cause a statement to recompile.
- Provide a simple programming model.
- Enable you to include complex business logic in a single routine.
- Reduce round trips to the server.
- Can have a table structure of different cardinality.
- Are strongly typed.
- Enable the client to specify sort order and unique keys.
Restricciones
- SQL Server does not maintain statistics on columns of table-valued parameters.
- Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines. You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.
- You cannot use a table-valued parameter as target of a SELECT INTO or INSERT EXEC statement. A table-valued parameter can be in the FROM clause of SELECT INTO or in the INSERT EXEC string or stored-procedure.
Ejemplo
USE AdventureWorks;
GO
/* Crear el tipo tabla. */
CREATE TYPE LocationTableType AS TABLE
(LocationName VARCHAR(50), CostRate INT);
GO
/*Crear un procedimiento almacenado que recibe el parámetro tabla.*/
CREATE PROCEDURE usp_InsertProductionLocation
@TVP LocationTableType READONLY
AS
SET NOCOUNT ON
INSERT INTO [AdventureWorks].[Production].[Location]
([Name], [CostRate], [Availability], [ModifiedDate])
SELECT *, 0, GETDATE()
FROM @TVP;
GO
/* Declarar variable de tipo tabla */
DECLARE @LocationTVP AS LocationTableType;
/* Añadir datos al tipo tabla */
INSERT INTO @LocationTVP (LocationName, CostRate)
SELECT [Name], 0.00
FROM
[AdventureWorks].[Person].[StateProvince];
/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;
GO