SDAC

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;
© 1997-2022 Devart. All Rights Reserved. Request Support DAC Forum Provide Feedback