Associação e transferência de dados de parâmetros com valor de tabela e valores de coluna

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureAzure Synapse AnalyticsPDW (Analytics Platform System)

Os parâmetros com valor de tabela (TVP), como outros parâmetros, devem ser associados antes de serem passados para o servidor. O aplicativo associa parâmetros com valor de tabela da mesma forma que associa outros parâmetros: usando SQLBindParameter ou chamadas equivalentes a SQLSetDescField ou SQLSetDescRec. O tipo de dados do servidor para um parâmetro com valor de tabela é SQL_SS_TABLE. O tipo C pode ser especificado como SQL_C_DEFAULT ou SQL_C_BINARY.

No SQL Server 2008 (10.0.x) ou posterior, há suporte apenas para parâmetros com valor de tabela de entrada. Portanto, qualquer tentativa de definir SQL_DESC_PARAMETER_TYPE para um valor diferente de SQL_PARAM_INPUT retorna SQL_ERROR com SQLSTATE = HY105 e a mensagem "Tipo de parâmetro inválido".

Valores padrão podem ser atribuídos a colunas inteiras de parâmetros com valor de tabela usando o atributo SQL_CA_SS_COL_HAS_DEFAULT_VALUE. No entanto, os valores de coluna de parâmetro com valor de tabela individual não podem ser atribuídos a valores padrão usando SQL_DEFAULT_PARAM em StrLen_or_IndPtr com SQLBindParameter. Parâmetros com valor de tabela como um todo não podem ser definidos como um valor padrão usando SQL_DEFAULT_PARAM em StrLen_or_IndPtr com SQLBindParameter. Se essas regras não forem seguidas, SQLExecute ou SQLExecDirect retornará SQL_ERROR. Um registro de diagnóstico é gerado com SQLSTATE=07S01 e a mensagem "Uso inválido do parâmetro padrão para o parâmetro <p>", em <que p> é o ordinal do TVP na instrução de consulta.

Observação

Os parâmetros com valor de tabela não têm um valor padrão que pode ser definido, pois SQL_DEFAULT_PARAM não indica linhas. Portanto, se não houver linhas, não haverá colunas a serem associadas.

Depois de associar o parâmetro com valor de tabela, o aplicativo deverá, então, associar cada coluna de parâmetros com valor de tabela. Para fazer isso, o aplicativo primeiro chama SQLSetStmtAttr para definir SQL_SOPT_SS_PARAM_FOCUS para o ordinal de um parâmetro com valor de tabela. O aplicativo associa as colunas do parâmetro com valor de tabela por chamadas às seguintes rotinas: SQLBindParameter, SQLSetDescRec e SQLSetDescField. Definir SQL_SOPT_SS_PARAM_FOCUS como 0 restaura o efeito usual de SQLBindParameter, SQLSetDescRec e SQLSetDescField na operação em parâmetros regulares de nível superior.

Observação

Para os drivers Linux e Mac ODBC com unixODBC 2.3.1 para 2.3.4, ao definir o nome TVP por meio de SQLSetDescField com o campo descritor SQL_CA_SS_TYPE_NAME, unixODBC não é convertido automaticamente entre cadeias de caracteres ANSI e Unicode, dependendo da função exata chamada (SQLSetDescFieldA / SQLSetDescFieldW). É necessário sempre usar SQLBindParameter ou SQLSetDescFieldW com uma cadeia de caracteres Unicode (UTF-16) para definir o nome TVP.

Nenhum dado real é enviado ou recebido para o próprio parâmetro com valor de tabela, mas dados são enviados e recebidos para cada uma de suas colunas constituintes. Como o parâmetro com valor de tabela é uma pseudo-coluna, os parâmetros para SQLBindParameter referem-se a atributos diferentes de outros tipos de dados, da seguinte maneira:

Parâmetro	Atributo relacionado para tipos de parâmetro não com valor de tabela, incluindo colunas	Atributo relacionado para parâmetros com valor de tabela
InputOutputType	SQL_DESC_PARAMETER_TYPE em IPD. Para colunas de parâmetros com valor de tabela, isso deve ser igual à configuração do próprio parâmetro com valor de tabela.	SQL_DESC_PARAMETER_TYPE em IPD. Isso deve ser SQL_PARAM_INPUT.
ValueType	SQL_DESC_TYPE, SQL_DESC_CONCISE_TYPE em APD.	SQL_DESC_TYPE, SQL_DESC_CONCISE_TYPE em APD. Isso deve ser SQL_C_DEFAULT ou SQL_C_BINARY.
ParameterType	SQL_DESC_TYPE, SQL_DESC_CONCISE_TYPE em IPD.	SQL_DESC_TYPE, SQL_DESC_CONCISE_TYPE em IPD. Isso deve ser SQL_SS_TABLE.
ColumnSize	SQL_DESC_LENGTH ou SQL_DESC_PRECISION em IPD. Isso depende do valor de ParameterType.	SQL_DESC_ARRAY_SIZE Também poderá ser definido usando SQL_ATTR_PARAM_SET_SIZE quando o foco do parâmetro for definido como o parâmetro com valor de tabela. Para um parâmetro com valor de tabela, esse é o número de linhas nos buffers de colunas de parâmetros com valor de tabela.
DecimalDigits	SQL_DESC_PRECISION ou SQL_DESC_SCALE em IPD.	Não utilizado. Isso deve ser 0. Se esse parâmetro não for 0, SQLBindParameter retornará SQL_ERROR e um registro de diagnóstico será gerado com SQLSTATE= HY104 e a mensagem "Precisão ou escala inválida".
ParameterValuePtr	SQL_DESC_DATA_PTR em APD.	SQL_CA_SS_TYPE_NAME. Isso é opcional para chamadas de procedimento armazenado e NULL pode ser especificado se não for necessário. Ele deve ser especificado para instruções SQL que não são chamadas de procedimento. Esse parâmetro também funciona como um valor exclusivo que o aplicativo poderá usar para identificar esse parâmetro com valor de tabela quando a associação de linha variável for usada. Para obter mais informações, consulte a seção "Associação de linha variável de parâmetros com valor de tabela", mais adiante neste tópico. Quando um nome de tipo de parâmetro com valor de tabela é especificado em uma chamada para SQLBindParameter, ele deve ser especificado como um valor Unicode, mesmo em aplicativos criados como aplicativos ANSI. O valor usado para o parâmetro StrLen_or_IndPtr deve ser SQL_NTS ou o comprimento da cadeia de caracteres do nome multiplicado pelo tamanho de (WCHAR).
BufferLength	SQL_DESC_OCTET_LENGTH em APD.	O comprimento do nome do tipo de parâmetro com valor de tabela em bytes. Isso poderá ser SQL_NTS se o nome do tipo for encerrado em nulo ou 0 se o nome do tipo de parâmetro com valor de tabela não for necessário.
Strlen_or_indptr	SQL_DESC_OCTET_LENGTH_PTR em APD.	SQL_DESC_OCTET_LENGTH_PTR em APD. Para parâmetros com valor de tabela, essa é uma contagem de linhas e não um comprimento de dados.

Existe suporte para dois modos de transferência de dados para parâmetros com valor de tabela: associação de linha fixa e associação de linha variável.

Associação de linha fixa de parâmetros com valor de tabela

Para associação de linha fixa, um aplicativo aloca buffers (ou matrizes de buffers) grandes o bastante para todos os possíveis valores de coluna de entrada. O aplicativo faz o seguinte:

1. Associa todos os parâmetros usando chamadas SQLBindParameter, SQLSetDescRec ou SQLSetDescField.

   1. Define SQL_DESC_ARRAY_SIZE como o número máximo de linhas que podem ser transferidas para cada parâmetro com valor de tabela. Isso pode ser feito na chamada SQLBindParameter.

2. Chama SQLSetStmtAttr para definir SQL_SOPT_SS_PARAM_FOCUS para o ordinal de cada parâmetro com valor de tabela.

   1. Para cada parâmetro com valor de tabela, associa colunas de parâmetro com valor de tabela usando chamadas SQLBindParameter, SQLSetDescRec ou SQLSetDescField.

   2. Para cada coluna de parâmetro com valor de tabela que deve ter valores padrão, chama SQLSetDescField para definir SQL_CA_SS_COL_HAS_DEFAULT_VALUE como 1.

3. Chama SQLSetStmtAttr para definir SQL_SOPT_SS_PARAM_FOCUS como 0. Isso deve ser feito antes que SQLExecute ou SQLExecDirect seja chamado. Caso contrário, SQL_ERROR é retornado e um registro de diagnóstico é gerado com SQLSTATE=HY024 e a mensagem "Valor de atributo inválido, SQL_SOPT_SS_PARAM_FOCUS (deve ser zero no momento da execução)."

4. Define StrLen_or_IndPtr ou SQL_DESC_OCTET_LENGTH_PTR para SQL_DEFAULT_PARAM para um parâmetro com valor de tabela sem linhas ou o número de linhas a serem transferidas na próxima chamada de SQLExecute ou SQLExecDirect se o parâmetro com valor de tabela tiver linhas. StrLen_or_IndPtr ou SQL_DESC_OCTET_LENGTH_PTR não podem ser definidos como SQL_NULL_DATA para um parâmetro com valor de tabela, pois parâmetros com valor de tabela não são anuláveis (embora colunas constituintes de parâmetro com valor de tabela possam ser anuláveis). Se isso for definido como um valor inválido, SQLExecute ou SQLExecDirect retornará SQL_ERROR e um registro de diagnóstico será gerado com SQLSTATE=HY090 e a mensagem "Cadeia de caracteres inválida ou comprimento de buffer para o parâmetro <p>", em que p é o número do parâmetro.

5. Chama SQLExecute ou SQLExecDirect.

   Os valores de coluna de parâmetro com valor de tabela de entrada poderão ser passados em partes se StrLen_or_IndPtr estiver definido como SQL_LEN_DATA_AT_EXEC(length) ou SQL_DATA_AT_EXEC para a coluna. Isso é semelhante a passar valores em partes quando são usadas matrizes de parâmetros. Assim como acontece com todos os parâmetros de dados em execução, SQLParamData não indica para qual linha da matriz o driver está solicitando dados; o aplicativo deve cuidar disso. O aplicativo não pode fazer nenhuma suposição sobre a ordem na qual o driver solicita valores.

Associação de linha variável de parâmetros com valor de tabela

Para associação de linha variável, as linhas são transferidas em lotes no momento da execução e o aplicativo passa linhas para o driver sob demanda. Isso é semelhante a dados em execução para valores de parâmetros individuais. Para associação de linha variável, o aplicativo faz o seguinte:

1. Associa parâmetros e colunas de parâmetro com valor de tabela, conforme descrito nas etapas 1 a 3 da seção anterior, "Correção Table-Valued associação de linha de parâmetro".

2. Define StrLen_or_IndPtr ou SQL_DESC_OCTET_LENGTH_PTR para todos os parâmetros com valor de tabela a serem passados no momento da execução para SQL_DATA_AT_EXEC. Se nenhum dos dois for definido, o parâmetro será processado conforme descrito na seção anterior.

3. Chama SQLExecute ou SQLExecDirect. Isso retornará SQL_NEED_DATA se houver algum parâmetro SQL_PARAM_INPUT ou SQL_PARAM_INPUT_OUTPUT a ser tratado como parâmetros de dados em execução. Nesse caso, o aplicativo faz o seguinte:

   • Chama SQLParamData. Isso retorna o valor ParameterValuePtr para um parâmetro data-at-execution e um código de retorno de SQL_NEED_DATA. Quando todos os dados de parâmetro foram passados para o driver, SQLParamData retorna SQL_SUCCESS, SQL_SUCCESS_WITH_INFO ou SQL_ERROR. Para parâmetros de dados em execução, ParameterValuePtr, que é o mesmo que o campo descritor SQL_DESC_DATA_PTR, pode ser considerado como um token para identificar um parâmetro para o qual um valor é necessário exclusivamente. Esse "token" é passado do aplicativo para o driver em tempo de associação e, depois, devolvido ao aplicativo em tempo de execução.

4. Para enviar dados de linha de parâmetro com valor de tabela para parâmetros com valor de tabela nulo, se o parâmetro com valor de tabela não tiver linhas, um aplicativo chamará SQLPutData com StrLen_or_Ind definido como SQL_DEFAULT_PARAM.

   Para TVPs não nulos, um aplicativo:

   • Define Str_Len_or_Ind para todas as colunas de parâmetro com valor de tabela para valores apropriados e preenche buffers de dados para colunas de parâmetro com valor de tabela que não devem ser parâmetros de dados em execução. Você pode usar dados em execução para colunas de parâmetros com valor de tabela de forma semelhante ao modo como parâmetros comuns podem ser passados para o driver em partes.

   • Chama SQLPutData com Str_Len_or_Ind definido como o número de linhas a serem enviadas ao servidor. Qualquer valor fora do intervalo de 0 a SQL_DESC_ARRAY_SIZE ou SQL_DEFAULT_PARAM é um erro e retorna SQLSTATE HY090, com a mensagem "Comprimento de buffer ou cadeia de caracteres inválida". 0 indica que todas as linhas foram enviadas e não há mais dados para um parâmetro com valor de tabela (conforme observado no segundo item de marcador nesta lista). SQL_DEFAULT_PARAM poderá ser usado apenas na primeira vez em que o driver solicitar dados para um parâmetro com valor de tabela (como descrito no primeiro item de marcador desta lista).

5. Quando todas as linhas tiverem sido enviadas, chamará SQLPutData para o parâmetro com valor de tabela com um valor de Str_Len_or_Ind de 0 e, em seguida, prossiga para a etapa 3a acima.

6. Chama SQLParamData novamente. Se houver parâmetros de dados em execução entre as colunas de parâmetro com valor de tabela, eles serão identificados pelo valor ValuePtrPtr retornado por SQLParamData. Quando todos os valores de coluna estão disponíveis, SQLParamData retorna o valor ParameterValuePtr para o parâmetro com valor de tabela e o aplicativo começa novamente.

Próximas etapas

Parâmetros com valor de tabela ODBC