Using Table-Valued Parameters

Table-valued parameters are a new parameter type introduced in SQL Server 2008. They can be used to send multiple rows of data to a Transact-SQL statement or a stored routine without creating a temporary table or many parameters. To learn more on table-valued parameters, see http://msdn.microsoft.com/en-us/library/bb510489.aspx

This topic demonstrates how to use table-valued parameters in your application by the help of SDAC

1. In order to pass a table as a parameter to a stored procedure or function, create a TABLE TYPE as follows:

   CREATE TYPE DeptTableType AS TABLE(
     DNAME VARCHAR(20),
     LOC VARCHAR(20)
   )

2. In a stored procedure we will transfer data from a parameter to a table on a server. Here is a script example for creating a table:

   CREATE TABLE DEPT(
     DEPTNO INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
     DNAME VARCHAR(20) NULL,
     LOC VARCHAR(20) NULL
   )

3. Create a stored procedure that uses the table type:

   CREATE PROCEDURE SP_InsertDept
     @TVP DeptTableType READONLY
   AS
   BEGIN
     INSERT INTO DEPT ([DNAME], [LOC])
       SELECT * FROM @TVP
   END

4. To work with Table-Valued Parameters, you should use the TMSTableData component. Fill it with data:

   MSTableData.TableTypeName := 'DeptTableType';
   MSTableData.Open;
   MSTableData.Append;
   MSTableData.Fields[0].AsString := 'ACCOUNTING';
   MSTableData.Fields[1].AsString := 'NEW YORK';
   MSTableData.Post;
   MSTableData.Append;
   MSTableData.Fields[0].AsString := 'RESEARCH';
   MSTableData.Fields[1].AsString := 'DALLAS';
   MSTableData.Post;
   MSTableData.Append;
   MSTableData.Fields[0].AsString := 'SALES';
   MSTableData.Fields[1].AsString := 'CHICAGO';
   MSTableData.Post;
   MSTableData.Append;
   MSTableData.Fields[0].AsString := 'OPERATIONS';
   MSTableData.Fields[1].AsString := 'BOSTON';
   MSTableData.Post;

5. Use the TMSStoredProc component to transfer data from the TMSTableData component to a table on a server:

   MSStoredProc.StoredProcName := 'SP_InsertDept';
   MSStoredProc.PrepareSQL;
   MSStoredProc.ParamByName('TVP').AsTable := MSTableData.Table;
   MSStoredProc.ExecProc;
   MSTableData.Close;