Sending Data as a Table-Valued Parameter with All Values in Memory (ODBC)

Prerequisite

This procedure assumes that the following Transact-SQL has been executed on the server:

create type TVParam as table(ProdCode integer, Qty integer)
create procedure TVPOrderEntry(@CustCode varchar(5), @Items TVPParam, 
            @OrdNo integer output, @OrdDate datetime output)
         as 
         set @OrdDate = GETDATE();
         insert into TVPOrd (OrdDate, CustCode) 
values (@OrdDate, @CustCode) output OrdNo); 
         select @OrdNo = SCOPE_IDENTITY(); 
         insert into TVPItem (OrdNo, ProdCode, Qty)
select @OrdNo, @Items.ProdCode, @Items.Qty 
from @Items

To Send the Data

1. Declare variables for the SQL parameters. In this case, the table value is held entirely in memory, so values for the columns of the table value are declared as arrays.

   SQLRETURN r;
   // Variables for SQL parameters.
   #define ITEM_ARRAY_SIZE 20
   
   SQLCHAR CustCode[6];
   SQLCHAR *TVP = (SQLCHAR *) "TVParam";
   SQLINTEGER ProdCode[ITEM_ARRAY_SIZE], Qty[ITEM_ARRAY_SIZE];
   SQLINTEGER OrdNo;
   char OrdDate[23];
   
   // Variables for indicator/length variables associated with parameters.
   SQLLEN cbCustCode, cbTVP, cbProdCode[ITEM_ARRAY_SIZE], cbQty[ITEM_ARRAY_SIZE], cbOrdNo, cbOrdDate;
   

2. Bind the parameters. Binding parameters is a two stage process when table-valued parameters are used. In the first stage, step parameters for the stored procedure are bound in the normal way, as follows.

   // Bind parameters for call to TVPOrderEntryDirect.
   // 1 - Custcode input
   r = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,SQL_VARCHAR, SQL_C_CHAR, 5, 0, CustCode, sizeof(CustCode), &cbCustCode);
   
   // 2 - Items TVP
   r = SQLBindParameter(hstmt, 
       2,// ParameterNumber
       SQL_PARAM_INPUT,// InputOutputType
       SQL_C_DEFAULT,// ValueType 
       SQL_SS_TABLE,// Parametertype
       ITEM_ARRAY_SIZE,// ColumnSize: For a table-valued parameter this is the row array size.
       0,// DecimalDigits: For a table-valued parameter this is always 0. 
       TVP,// ParameterValuePtr: For a table-valued parameter this is the type name of the 
   //table-valued parameter, and also a token returned by SQLParamData.
       SQL_NTS,// BufferLength: For a table-valued parameter this is the length of the type name or SQL_NTS.
       &cbTVP);// StrLen_or_IndPtr: For a table-valued parameter this is the number of rows actually used.
   
   // 3 - OrdNo output
   r = SQLBindParameter(hstmt, 3, SQL_PARAM_OUTPUT,SQL_INTEGER, SQL_C_LONG, 0, 0, &OrdNo,
      sizeof(SQLINTEGER), &cbOrdNo);
   // 4 - OrdDate output
   r = SQLBindParameter(hstmt, 4, SQL_PARAM_OUTPUT,SQL_TYPE_TIMESTAMP, SQL_C_CHAR, 23, 3, &OrdDate, 
      sizeof(OrdDate), &cbOrdDate);
   

3. The second stage of parameter binding is to bind the columns for the table-valued parameter. The parameter focus is first set to the ordinal of the table-valued parameter. Then columns of the table value are bound by using SQLBindParameter in the same way as they would be if they were parameters of the stored procedure, but with column ordinals for ParameterNumber. If there were more table-valued parameters, we would set the focus to each in turn and bind their columns. Finally, the parameter focus is reset to 0.

   // Bind columns for the table-valued parameter (param 2).
   // First set focus on param 2.
   r = SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER) 2, SQL_IS_INTEGER);
   
   // Col 1 - ProdCode
   r = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,SQL_INTEGER, SQL_C_LONG, 0, 0, ProdCode, sizeof(SQLINTEGER), cbProdCode);
   // Col 2 - Qty
   r = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT,SQL_INTEGER, SQL_C_LONG, 0, 0, Qty, sizeof(SQLINTEGER), cbQty);
   
   // Reset param focus.
   r = SQLSetStmtAttr(hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER) 0, SQL_IS_INTEGER);
   

4. Populate the parameter buffers. cbTVP is set to the number of rows to be sent to the server.

   // Populate parameters.
   cbTVP = 0; // Number of rows available for input.
   strcpy_s((char *) CustCode, sizeof(CustCode), "CUST1"); cbCustCode = SQL_NTS;
   
   ProdCode[cbTVP] = 1215;cbProdCode[cbTVP] = sizeof(SQLINTEGER); 
   Qty[cbTVP] = 5;cbQty[cbTVP] = sizeof(SQLINTEGER); 
   cbTVP++; // Number of rows available for input
   
   ProdCode[cbTVP] = 1017;cbProdCode[cbTVP] = sizeof(SQLINTEGER); 
   Qty[cbTVP] = 2;cbQty[cbTVP] = sizeof(SQLINTEGER); 
   cbTVP++; // Number of rows available for input.
   

5. Call the procedure:

   // Call the procedure.
   r = SQLExecDirect(hstmt, (SQLCHAR *) "{call TVPOrderEntry(?, ?, ?, ?)}",SQL_NTS);
   

See Also

Concepts