* FILENAME :    TVPInsertRecs.c
*               Demonstrates a simple example of using a Table-Valued Parameter
*               (TVP) to populate a table using a stored procedure with just one
*               parameter. TVPs were introduced in SQL SERVER 2008. A TVP is a
*               data type that can be used to hold records in a 'table' like
*               manner but are held within a variable inside a program. The
*               whole of the 'table' like parameter can then be passed as a
*               single parameter to a stored procedure for processing. For
*               example, TVPs can be usefully applied when performing bulk data
*               operations where a whole set of rows can be passed as a single
*               parameter to a stored procedure for subsequent inserting into
*               a database table.
*               The TVP used here represents a table type with 4 columns:
*                   FirstName, LastName, Address and City.
*               Prompts user for number of records to create
*               Sets up the test by dropping and re-creating:-
*                   1. Database Table     (TestTVPTable1) - into which rows
*                      are written from rows in a TVP.
*                   2. Stored Procedure   (InsertFromTVP) - to select records
*                      from the TVP and insert them into TestTVPTable1.
*                   3. TVP Table Variable (PersonTVP)     - Table variable to
*                      hold records and passed as a single parameter to
*                      InsertFromTVP().
*               Bind Paramaters:
*                   Binds parameter 1 as a TVP
*               Bind TVP Parameters:
*                  Sets focus on parameter 1
*                  Bind arrays to each of the 4 TVP columns and lengths. i.e.
*                  for FirstName, LastName, Address and City
*                  Clears focus by setting focus to zero
*               Load data into the TVP bound arrays:
*                  For the number of records requested
*                  prompts user for each of the four columns per row
*                  and save each set in the next row of the TVP bound buffers

*               Execute the stores procedure to insert the records stored in the
*               TVP bound array buffers. Note the stored procedure only takes
*               one parameter, the TVP, but upto as many as MAX_ARRAY_SIZE records
*               are written to the table.

#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlext.h>
#include <sqlncli.h>
#include <string.h>

#include "util.c"

#define LASTNAME_LEN  255
#define FIRSTNAME_LEN 255
#define ADDRESS_LEN 255
#define CITY_LEN  255

#define MAX_ARRAY_SIZE 20

// SetupTVPTest is used to create and/or drop the stored procedure,
// TVP table type and DB Table used in the test.
SQLRETURN SetupTVPTest (SQLHDBC hdbc, char createEm, char dropEm) {

    SQLHSTMT hstmt = SQL_NULL_HSTMT;  	// Statement handle
    SQLRETURN retcode = SQL_SUCCESS;	// Return status

    // Drop statements
    // Stored Procedure
    SQLCHAR strDropTVPProc [] =
        "IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = "
        "object_id(N'dbo.InsertFromTVP') AND OBJECTPROPERTY"
        "(id, N'IsProcedure') = 1) DROP PROCEDURE dbo.InsertFromTVP";

    // TVP Table Type
    SQLCHAR strDropTVPType[] =
        "IF EXISTS (SELECT * FROM sys.types WHERE "
        "is_table_type = 1 AND name = 'PersonTVP') "
        "DROP TYPE dbo.PersonTVP";

    // TVP Table (the physical one in the DB)
    SQLCHAR strDropTVPTable [] =
        "IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = "
        "object_id(N'dbo.TestTVPTable1') AND OBJECTPROPERTY(id, N'IsUserTable')"
        " = 1) DROP TABLE dbo.TestTVPTable1";

    // Create the physical database table
    SQLCHAR strCreateTVPTable [] =
        "CREATE TABLE TestTVPTable1 ("
            "PersonID int NOT NULL IDENTITY(1,1),"
            "FirstName   varchar(255),"
            "LastName    varchar(255),"
            "Address     varchar(255),"
            "City        varchar(255)"

    // Create the in-memory Table-Value Parameter Table
    SQLCHAR strCreateTVPType [] =
        "CREATE TYPE PersonTVP AS TABLE ( "
            "vFirstName  varchar(255),"
            "vLastName   varchar(255),"
            "vAddress    varchar(255),"
            "vCity       varchar(255)"

    // Create the stored procedure which takes one parameter,
    // the persons Table-Value Parameter Table.
    // NOTE: Table-value Params Must be read only.
    SQLCHAR strCreateTVPProc [] =
        "CREATE PROCEDURE InsertFromTVP (@Persons PersonTVP READONLY) "
        "AS "
            "INSERT INTO TestTVPTable1 (FirstName, LastName, Address, City) "
            "SELECT vFirstName, vLastName, vAddress, vCity FROM @Persons";

    // Allocate a statement handle
    retcode=SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
    CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_STMT)",
                hstmt, SQL_HANDLE_STMT);

    if (dropEm=='Y') {
    	// Execute Drop Procedure
    	retcode = SQLExecDirect (hstmt, strDropTVPProc, SQL_NTS);
        CHECK_ERROR(retcode, "SQLExecDirect(DROPPROC)",
                    hstmt, SQL_HANDLE_STMT);

    	// Execute Drop Type
    	retcode = SQLExecDirect (hstmt, strDropTVPType, SQL_NTS);
        CHECK_ERROR(retcode, "SQLExecDirect(DROPTYPE)",
                    hstmt, SQL_HANDLE_STMT);

    	// Execute DROP Table
    	retcode = SQLExecDirect (hstmt, strDropTVPTable, SQL_NTS);
        CHECK_ERROR(retcode, "SQLExecDirect(DROPTABLE)",
                    hstmt, SQL_HANDLE_STMT);

    if (createEm=='Y')

        // Execute Create Table
        retcode = SQLExecDirect (hstmt, strCreateTVPTable, SQL_NTS);
        CHECK_ERROR(retcode, "SQLExecDirect(CREATETABLE)",
                    hstmt, SQL_HANDLE_STMT);

        // Execute Create Type
        retcode = SQLExecDirect (hstmt, strCreateTVPType, SQL_NTS);
        CHECK_ERROR(retcode, "SQLExecDirect(CREATETYPE)",
                    hstmt, SQL_HANDLE_STMT);

        // Execute Create Procedure
        retcode = SQLExecDirect (hstmt, strCreateTVPProc, SQL_NTS);
        CHECK_ERROR(retcode, "SQLExecDirect(CREATEPROC)",
                    hstmt, SQL_HANDLE_STMT);

    // Free statement handle
    if (hstmt != SQL_NULL_HSTMT)
        SQLFreeHandle(SQL_HANDLE_STMT, hstmt);

    return retcode;

// Arrays of column values

// Arrays of column values lengths

int main () {

    SQLHENV  henv  = SQL_NULL_HENV;   	// Environment
    SQLHDBC  hdbc  = SQL_NULL_HDBC;   	// Connection handle
    SQLHSTMT hstmt = SQL_NULL_HSTMT;  	// Statement handle
    SQLRETURN retcode;			// Return status

    int  i, numRows=1;
    // The name of the TVP table data type
    SQLCHAR *TVPTableName = (SQLCHAR *) "PersonTVP";
    SQLLEN  lTVPRowsUsed;

    // Get Number of records to insert
    getInt ("Number of Recs", &numRows, 'N', 0);
    if (numRows==0) goto exit;
    if (numRows>MAX_ARRAY_SIZE) numRows=MAX_ARRAY_SIZE;

    // Allocate environment
    retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
    CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_ENV)",
                henv, SQL_HANDLE_ENV);

    // Set ODBC Verion
    retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
                            (SQLPOINTER*)SQL_OV_ODBC3, 0);
                henv, SQL_HANDLE_ENV);

    // Allocate Connection
    retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
    CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_DBC)",
                henv, SQL_HANDLE_DBC);

    // Set Login Timeout
    retcode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);
    CHECK_ERROR(retcode, "SQLSetConnectAttr(SQL_LOGIN_TIMEOUT)",
                hdbc, SQL_HANDLE_DBC);

    // Connect to DSN
    retcode = SQLConnect(hdbc, (SQLCHAR*) "SQLSRV2014", SQL_NTS,
                         (SQLCHAR*) NULL, 0, NULL, 0);
    CHECK_ERROR(retcode, "SQLConnect(DSN:SQLSRV2014;)",
                hdbc, SQL_HANDLE_DBC);

    // Allocate Statement Handle
    retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
    CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_STMT)",
                hstmt, SQL_HANDLE_STMT);

    // Setup stored procedure, Table-Value Parameter data type and database
    // Table.
    // Second parameter indicates whether the above are to be created
    // Third parameter indicates whether the above are to be dropped
    retcode = SetupTVPTest (hdbc, 'Y', 'Y');
    CHECK_ERROR(retcode, "SetupTVPTest()", hdbc, SQL_HANDLE_DBC);

    // 1st step is bind all parameters in normal way.
    // Here we have just one parameter, a Table-Valued Parameter (TVP)
    // which requires some specific parameters.
    retcode = SQLBindParameter(
                hstmt,              // Statement handle
                1,                  // Parameter Number
                SQL_PARAM_INPUT,    // Input/Output Type (always INPUT for TVP)
                SQL_C_DEFAULT,      // C - Type (always this for a TVP)
                SQL_SS_TABLE,       // SQL Type (always this for a TVP)
                MAX_ARRAY_SIZE,     // For a TVP this is max rows we will use
                0,                  // For a TVP this is always 0
                TVPTableName,       // For a TVP this is the type name of the
                                    // TVP, and also a token returned by
                                    // SQLParamData.
                SQL_NTS,            // For a TVP this is the length of the type
                                    // name or SQL_NTS.
                &lTVPRowsUsed);     // For a TVP this is the number of rows
                                    // actually available.

    // Now we need to bind a separate set of parameters for each TVP.
    // To do we set the focus on each TVP in turn, bind data to it in the same
    // way as for rows in a table.
    // First set focus on param 1.
    retcode = SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS,
                             (SQLPOINTER) 1, SQL_IS_INTEGER);

    // Col 1 - Bind start of FirstName array
    retcode = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,
                               SQL_C_CHAR, SQL_CHAR,
                               FIRSTNAME_LEN, 0, &strFirstName[0],
                               FIRSTNAME_LEN, &lFirstName[0]);
    // Col 2 - Bind start of LastName array
    retcode = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT,
                               SQL_C_CHAR, SQL_CHAR,
                               LASTNAME_LEN, 0, &strLastName[0],
                               LASTNAME_LEN, &lLastName[0]);
    // Col 3 - Bind start of Address array
    retcode = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT,
                               SQL_C_CHAR, SQL_CHAR, ADDRESS_LEN,
                               0, &strAddress[0],
                               ADDRESS_LEN, &lAddress[0]);
    // Col 4 - Bind start of City array
    retcode = SQLBindParameter(hstmt, 4, SQL_PARAM_INPUT,
                               SQL_C_CHAR, SQL_CHAR, CITY_LEN,
                               0, &strCity[0], CITY_LEN, &lCity[0]);

    // If there were further TVPs we would set the focus to the next one,
    // bind buffers to that, and so on on until all TVPs have bound buffers.
    // As we have no more, reset param focus to zero.
    retcode = SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS,
                             (SQLPOINTER) 0, SQL_IS_INTEGER);

    // The data for each row of the TVP is represented by the nth element of
    // the arrays bound for columns FirstName, LastName, Address and City,
    // held in strFirtstName[n], strLastName[n], strAddress[n], and strCity[n]
    // Prompt for data, filling columns and rows until numRows reached.
    for (i=0;i<numRows;i++) {
        // Clear Buffers
        memset (strFirstName[i], ' ', FIRSTNAME_LEN);
        memset (strLastName[i], ' ', LASTNAME_LEN);
        memset (strAddress[i], ' ', ADDRESS_LEN);
        memset (strCity[i], ' ', CITY_LEN);

        // Get data from user
        printf ("\nRecord %i, out of %i\n\n", i+1, numRows);
        getStr ("First Name", strFirstName[i], FIRSTNAME_LEN, 'N');
        getStr ("Last  Name", strLastName[i], LASTNAME_LEN, 'N');
        getStr ("Address   ", strAddress[i], ADDRESS_LEN, 'N');
        getStr ("City      ", strCity[i], CITY_LEN, 'N');

        // Set lengths

    // We now have arrays holding FirstNames, Lastnames, Addresses and Citys,
    // and corresponding lengths for each.
    // Set the StrLen_or_IndPtr value to the actual number of rows to write
    // (i.e. in the buffers) and execute the stored procedure. Although just
    // one parameter is passed, because it is a TVP, several rows are actually
    // written to the table.
    retcode = SQLExecDirect(hstmt, "{CALL InsertFromTVP(?)}", SQL_NTS);
    CHECK_ERROR(retcode, "SQLExecDirect(InsertFromTVP)",
                hstmt, SQL_HANDLE_STMT);


    printf ("\nComplete.\n");

    // Free handles
    // Statement
    if (hstmt != SQL_NULL_HSTMT)
        SQLFreeHandle(SQL_HANDLE_STMT, hstmt);

    // Connection
    if (hdbc != SQL_NULL_HDBC) {
        SQLFreeHandle(SQL_HANDLE_DBC, hdbc);

    // Environment
    if (henv != SQL_NULL_HENV)
        SQLFreeHandle(SQL_HANDLE_ENV, henv);

    return 0;

See Also

Oracle is a registered trademark of Oracle Corporation and/or its affiliates.