Skip to content

sending-data-as-a-table-valued-parameter-with-all-values-in-memory-odbc.md

Latest commit

 

History

History
128 lines (104 loc) · 6.22 KB

sending-data-as-a-table-valued-parameter-with-all-values-in-memory-odbc.md

File metadata and controls

128 lines (104 loc) · 6.22 KB
description Sending Data as a Table-Valued Parameter with All Values in Memory (ODBC)
title Table-Valued Parameter, values in Memory (ODBC)
ms.custom
ms.date 03/14/2017
ms.prod sql
ms.prod_service database-engine, sql-database, synapse-analytics, pdw
ms.reviewer
ms.technology native-client
ms.topic reference
helpviewer_keywords
table-valued parameters (ODBC), sending data to a stored procedure with all values in memory
ms.assetid 8b96282f-00d5-4e28-8111-0a87ae6d7781
author markingmyname
ms.author maghan
monikerRange >=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current

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

[!INCLUDESQL Server Azure SQL Database Synapse Analytics PDW ]

This topic describes how to send data to a stored procedure as a table-valued parameter when all values are in memory. For another sample demonstrating table-valued parameters, see Use Table-Valued Parameters (ODBC).

Prerequisite

This procedure assumes that the following [!INCLUDEtsql] 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

ODBC Table-Valued Parameter Programming Examples