sp_describe_undeclared_parameters (Transact-SQL)

Aplica-se a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsEndpoint de análise SQL no Microsoft FabricArmazém no Microsoft FabricBase de dados SQL no Microsoft Fabric

Devolve um conjunto de resultados que contém metadados sobre parâmetros não declarados num lote Transact-SQL. Considera cada parâmetro usado no lote @tsql , mas não declarado no @params. É devolvido um conjunto de resultados que contém uma linha para cada parâmetro desse tipo, com a informação do tipo deduzida para esse parâmetro. O procedimento devolve um conjunto de resultados vazio se o lote de entrada @tsql não tiver parâmetros exceto os declarados em @params.

Transact-SQL convenções de sintaxe

Sintaxe

sp_describe_undeclared_parameters
    [ @tsql = ] 'Transact-SQL_batch'
    [ , [ @params = ] N'@parameter_name data_type [ , ... n ]' ]

Observação

Para usar este procedimento armazenado no Azure Synapse Analytics num pool SQL dedicado, defina o nível de compatibilidade da base de dados como 20 ou superior. Para optar por não participar, altere o nível de compatibilidade da base de dados para 10.

Arguments

Importante

Os argumentos para procedimentos armazenados estendidos devem ser inseridos na ordem específica, conforme descrito na seção de sintaxe. Se os parâmetros forem inseridos fora de ordem, ocorrerá uma mensagem de erro.

@tsql [ = ] 'tsql'

Uma ou mais Transact-SQL declarações. @tsql pode ser nvarchar(n) ou nvarchar(max).

[ @params = ] N'@parameter_namedata_type [ ,... n ]»

@params fornece uma cadeia de declaração para parâmetros para o lote Transact-SQL, de forma semelhante ao funcionamento sp_executesql do processo. @params pode ser nvarchar(n) ou nvarchar(max).

Uma cadeia que contém as definições de todos os parâmetros que estão embutidos em @tsql. A cadeia de caracteres deve ser uma constante Unicode ou uma variável Unicode. Cada definição de parâmetro consiste em um nome de parâmetro e um tipo de dados. n é um espaço reservado que indica definições de parâmetros adicionais. Se a instrução Transact-SQL ou lote na instrução não contiver parâmetros, @params não será necessário. O valor padrão para esse parâmetro é NULL.

Valores de código de retorno

sp_describe_undeclared_parameters Devolve sempre o estado de zero em caso de sucesso. Se o procedimento gerar um erro e o procedimento for chamado como RPC, o estado de retorno é preenchido pelo tipo de erro descrito na error_type coluna de sys.dm_exec_describe_first_result_set. Se o procedimento for chamado a partir do Transact-SQL, o valor de retorno é sempre zero, mesmo em casos de erro.

Conjunto de resultados

sp_describe_undeclared_parameters retorna o seguinte conjunto de resultados.

Nome da coluna	Tipo de dados	Description
parameter_ordinal	int	Contém a posição ordinal do parâmetro no conjunto de resultados. A posição do primeiro parâmetro é especificada como 1. Não anulável.
name	sysname	Contém o nome do parâmetro. Não anulável.
suggested_system_type_id	int	Contém o system_type_id do tipo de dados do parâmetro especificado em sys.types. Para tipos CLR, mesmo que a system_type_name coluna devolva NULL, esta coluna devolve o valor 240. Não anulável.
suggested_system_type_name	Nvarchar(256)	Contém o nome do tipo de dados. Inclui argumentos (como comprimento, precisão, escala) especificados para o tipo de dado do parâmetro. Se o tipo de dado for um tipo de alias definido pelo utilizador, o tipo de sistema subjacente é especificado aqui. Se for um tipo de dado CLR definido pelo utilizador, NULL é devolvido nesta coluna. Se o tipo do parâmetro não puder ser deduzido, NULL é devolvido. Nullable.
suggested_max_length	smallint	Consulte sys.columns. para max_length a descrição da coluna. Não anulável.
suggested_precision	tinyint	Consulte sys.columns. para descrição precisa da coluna. Não anulável.
suggested_scale	tinyint	Consulte sys.columns. Para a descrição da coluna de escala. Não anulável.
suggested_user_type_id	int	Para CLR e tipos de alias, contém o user_type_id do tipo de dados da coluna conforme especificado em sys.types. Caso contrário, é NULL. Nullable.
suggested_user_type_database	sysname	Para CLR e tipos de alias, contém o nome da base de dados onde o tipo está definido. Caso contrário, é NULL. Nullable.
suggested_user_type_schema	sysname	Para tipos CLR e alias, contém o nome do esquema em que o tipo está definido. Caso contrário, é NULL. Nullable.
suggested_user_type_name	sysname	Para CLR e tipos de alias, contém o nome do tipo. Caso contrário, é NULL.
suggested_assembly_qualified_type_name	Nvarchar(4000)	Para tipos CLR, devolve o nome da assembly e da classe que define o tipo. Caso contrário, é NULL. Nullable.
suggested_xml_collection_id	int	Contém o xml_collection_id do tipo de dados do parâmetro especificado em sys.columns. Esta coluna retorna NULL se o tipo devolvido não estiver associado a uma coleção de esquemas XML. Nullable.
suggested_xml_collection_database	sysname	Contém a base de dados onde está definida a coleção de esquemas XML associada a este tipo. Esta coluna retorna NULL se o tipo devolvido não estiver associado a uma coleção de esquemas XML. Nullable.
suggested_xml_collection_schema	sysname	Contém o esquema em que a coleção de esquemas XML associada a este tipo está definida. Esta coluna retorna NULL se o tipo devolvido não estiver associado a uma coleção de esquemas XML. Nullable.
suggested_xml_collection_name	sysname	Contém o nome da coleção de esquemas XML associada a este tipo. Esta coluna retorna NULL se o tipo devolvido não estiver associado a uma coleção de esquemas XML. Nullable.
suggested_is_xml_document	bit	Retorna 1 se o tipo devolvido for XML e esse tipo for garantido como um documento XML. Caso contrário, retorna 0. Não anulável.
suggested_is_case_sensitive	bit	Retorna 1 se a coluna for de um tipo de cadeia com sensibilidade a maiúsculas minúsculas e 0 se não for. Não anulável.
suggested_is_fixed_length_clr_type	bit	Retorna 1 se a coluna for do tipo CLR de comprimento fixo e 0 se não for. Não anulável.
suggested_is_input	bit	Retorna 1 se o parâmetro for usado em qualquer lugar que não seja o lado esquerdo de uma atribuição. Caso contrário, retorna 0. Não anulável.
suggested_is_output	bit	Retorna 1 se o parâmetro for usado no lado esquerdo de uma atribuição ou for passado para um parâmetro de saída de um procedimento armazenado. Caso contrário, retorna 0. Não anulável.
formal_parameter_name	sysname	Se o parâmetro for um argumento para um procedimento armazenado ou uma função definida pelo utilizador, devolve o nome do parâmetro formal correspondente. Caso contrário, retorna NULL. Nullable.
suggested_tds_type_id	int	Para utilização interna. Não anulável.
suggested_tds_length	int	Para utilização interna. Não anulável.

Observações

sp_describe_undeclared_parameters devolve sempre o estado de zero.

O uso mais comum é quando uma aplicação recebe uma instrução Transact-SQL que pode conter parâmetros e deve processá-los de alguma forma. Um exemplo é uma interface de utilizador (como ODBCTest ou RowsetViewer) onde o utilizador fornece uma consulta com a sintaxe dos parâmetros ODBC. A aplicação deve descobrir dinamicamente o número de parâmetros e pedir ao utilizador cada um deles.

Outro exemplo é quando, sem entrada do utilizador, uma aplicação tem de fazer looping sobre os parâmetros e obter os dados de outro local (como uma tabela). Neste caso, a aplicação não tem de passar toda a informação dos parâmetros de uma só vez. Em vez disso, a aplicação pode obter toda a informação dos parâmetros do fornecedor e obter os próprios dados da tabela. O uso sp_describe_undeclared_parameters de código é mais genérico e é menos provável que exija modificações se a estrutura de dados mudar mais tarde.

sp_describe_undeclared_parameters devolve um erro em qualquer um dos casos seguintes.

• O @tsql de entrada não é um lote de Transact-SQL válido. A validade é determinada pela análise e análise do Transact-SQL lote. Quaisquer erros causados pelo lote durante a otimização da consulta ou durante a execução não são considerados ao determinar se o lote Transact-SQL é válido.

• @params não NULL é e contém uma cadeia que não é sintaticamente válida para parâmetros, ou se contiver uma cadeia que declare qualquer parâmetro mais do que uma vez.

• A entrada Transact-SQL lote declara uma variável local com o mesmo nome como um parâmetro declarado em @params.

• A declaração faz referência a tabelas temporárias.

• A consulta inclui a criação de uma tabela permanente que é depois consultada.

Se @tsql não tiver parâmetros, exceto parâmetros declarados em @params, o procedimento devolve um conjunto de resultados vazio.

Observação

Deve declarar a variável como uma variável escalar Transact-SQL, caso contrário surge um erro.

Algoritmo de seleção de parâmetros

Para uma consulta com parâmetros não declarados, a dedução do tipo de dado para parâmetros não declarados decorre em três passos.

Passo 1: Encontrar os tipos de dados das subexpressões

O primeiro passo na dedução de tipos de dados para uma consulta com parâmetros não declarados é encontrar os tipos de dados de todas as subexpressões cujos tipos de dados não dependem dos parâmetros não declarados. O tipo pode ser determinado para as seguintes expressões:

• Colunas, constantes, variáveis e parâmetros declarados.
• Resultados de uma chamada a uma função definida pelo utilizador (UDF).
• Uma expressão com tipos de dados que não dependem dos parâmetros não declarados para todas as entradas.

Por exemplo, considere a consulta SELECT dbo.tbl(@p1) + c1 FROM t1 WHERE c2 = @p2 + 2. As expressões dbo.tbl(@p1) + c1 e c2 têm tipos de dados, e expressão @p1 e @p2 + 2 não.

Após este passo, se qualquer expressão (exceto uma chamada para um UDF) tiver dois argumentos sem tipos de dados, a dedução de tipo falha com um erro. Por exemplo, todos os seguintes produzem erros:

SELECT * FROM t1 WHERE @p1 = @p2;
SELECT * FROM t1 WHERE c1 = @p1 + @p2;
SELECT * FROM t1 WHERE @p1 = SUBSTRING(@p2, 2, 3);

O exemplo seguinte não produz erro:

SELECT * FROM t1 WHERE @p1 = dbo.tbl(c1, @p2, @p3);

Passo 2: Encontrar expressões mais íntimas

Para um dado parâmetro @pnão declarado , o algoritmo de dedução de tipo encontra a expressão E(@p) mais interna que contém @p e é um dos seguintes argumentos:

• Um argumento para um operador de comparação ou atribuição.
• Um argumento para uma função definida pelo utilizador (incluindo UDF com valores de tabela), procedimento ou método.
• Um argumento para uma VALUES cláusula de uma INSERT afirmação.
• Um argumento para um CAST ou CONVERT.

O algoritmo de dedução de tipo encontra um tipo TT(@p) de dado alvo para E(@p). Os tipos de dados-alvo para os exemplos anteriores são os seguintes:

• O tipo de dados do outro lado da comparação ou atribuição.
• O tipo de dado declarado do parâmetro ao qual este argumento é passado.
• O tipo de dado da coluna onde este valor é inserido.
• O tipo de dado para o qual a instrução está a ser convertida ou convertida.

Por exemplo, considere a consulta SELECT * FROM t WHERE @p1 = dbo.tbl(@p2 + c1). Então , , é o tipo de dado de retorno declarado de dbo.tbl, e TT(@p2) é o tipo de dado de parâmetro declarado para dbo.tbl. TT(@p1)E(@p2) = @p2 + c1E(@p1) = @p1

Se @p não estiver contida em nenhuma expressão listada no início do passo 2, o algoritmo de dedução de tipos determina que E(@p) é a maior expressão escalar que contém @p, e o algoritmo de dedução de tipo não calcula um tipo TT(@p) de dado alvo para E(@p). Por exemplo, se a consulta for SELECT @p + 2 então E(@p) = @p + 2, e não TT(@p)existe .

Passo 3: Deduzir os tipos de dados

Agora que E(@p) e TT(@p) estão identificados, o algoritmo de dedução de tipo deduz um tipo de dado para @p de uma das seguintes duas formas:

• Dedução simples

  Se E(@p) = @p e TT(@p) existe, isto é, se @p for diretamente um argumento de uma das expressões listadas no início do passo 2, o algoritmo de dedução de tipo deduz o tipo de dados de @p como TT(@p)sendo . Por exemplo:

  SELECT * FROM t WHERE c1 = @p1 AND @p2 = dbo.tbl(@p3);
  

  O tipo de dados para @p1, @p2, e @p3 será o tipo de dados de c1, o tipo de dados de retorno de dbo.tbl, e o tipo de dado de parâmetro para dbo.tbl respetivamente.

  Como caso especial, se @p for um argumento para um <, >, <=, ou >= operador, regras simples de dedução não se aplicam. O algoritmo de dedução por tipo utilizará as regras gerais de dedução explicadas na secção seguinte. Por exemplo, se c1 for uma coluna do tipo de dados char(30), considere as seguintes duas consultas:

  SELECT * FROM t WHERE c1 = @p;
  SELECT * FROM t WHERE c1 > @p;
  

  No primeiro caso, o algoritmo de dedução por tipo deduz char(30) como o tipo de dados para @p conforme as regras anteriores deste artigo. No segundo caso, o algoritmo de dedução por tipo deduz varchar(8000) de acordo com as regras gerais de dedução na secção seguinte.

• Dedução geral

  Se a dedução simples não se aplicar, os seguintes tipos de dados são considerados para parâmetros não declarados:

  • Tipos de dados inteiros (bit, tinyint, smallint, int,bigint)

  • Tipos de dados monetários (smallmoney, money)

  • Tipos de dados em ponto flutuante (flutuante, real)

  • numérico(38, 19) - Outros tipos de dados numéricos ou decimais não são considerados.

  • varchar(8000), varchar(max), nvarchar(4000) e nvarchar(max) - Outros tipos de dados de cadeia (como text, char(8000), nvarchar(30), etc.) não são considerados.

  • varbinary(8000) e varbinary(max) - Outros tipos de dados binários não são considerados (como image,binary(8000), varbinary(30), etc.).

  • data, hora(7), littledatetime, datetime, datetime2(7), datetimeoffset(7) - Outros tipos de data e hora, como hora(4), não são considerados.

  • sql_variant

  • xml

  • Tipos definidos pelo sistema CLR (hierarchyid, geometria, geografia)

  • Tipos definidos pelo usuário CLR

Critérios de seleção

Dos tipos de dados candidatos, qualquer tipo que invalidasse a consulta é rejeitado. Dos restantes tipos de dados candidatos, o algoritmo de dedução de tipo seleciona um segundo as seguintes regras.

1. É selecionado o tipo de dado que produz o menor número de conversões implícitas em E(@p) . Se um determinado tipo de dados produz um tipo de dados para E(@p) que é diferente de TT(@p), o algoritmo de dedução de tipo considera isto como uma conversão implícita extra do tipo de dados de E(@p) para TT(@p).

   Por exemplo:

   SELECT * FROM t WHERE Col_Int = Col_Int + @p;
   

   Neste caso, E(@p) é Col_Int + @p e TT(@p) é int. int é escolhido para @p porque não produz conversões implícitas. Qualquer outra escolha de tipo de dado produz pelo menos uma conversão implícita.

2. Se múltiplos tipos de dados empatarem para o menor número de conversões, utiliza-se o tipo de dado com maior precedência. Por exemplo:

   SELECT * FROM t WHERE Col_Int = Col_smallint + @p;
   

   Neste caso, int e smallint produzem uma conversão. Todos os outros tipos de dados produzem mais do que uma conversão. Como int tem precedência sobre smallint, int é usado para @p. Para mais informações sobre precedência de tipos de dados, consulte precedência de tipo de dados.

   Esta regra só se aplica se houver uma conversão implícita entre cada tipo de dado que corresponde à regra 1 e o tipo de dado com maior precedência. Se não houver conversão implícita, então a dedução do tipo de dado falha com um erro. Por exemplo, na consulta SELECT @p FROM t, a dedução do tipo de dado falha porque qualquer tipo de dado para @p seria igualmente bom. Por exemplo, não há conversão implícita de int para xml.

3. Se dois tipos de dados semelhantes se associarem sob a regra 1, por exemplo varchar(8000) e varchar(max), o tipo de dado mais pequeno (varchar(8000)) é escolhido. O mesmo princípio aplica-se aos tipos de dados nvarchar e varbinary .

4. Para efeitos da regra 1, o algoritmo de dedução por tipo prefere certas conversões como melhores do que outras. As conversões por ordem do melhor para o pior são:

   1. Conversão entre o mesmo tipo de dado básico de comprimento diferente.
   2. Conversão entre versões de comprimento fixo e de comprimento variável dos mesmos tipos de dados (por exemplo, char para varchar).
   3. Conversão entre NULL e inteligência.
   4. Qualquer outra conversão.

Por exemplo, para a consulta SELECT * FROM t WHERE [Col_varchar(30)] > @p, varchar(8000) é escolhido porque a conversão (a) é a melhor. Para a consulta SELECT * FROM t WHERE [Col_char(30)] > @p, varchar(8000) ainda é escolhido porque causa uma conversão tipo (b), e porque outra escolha (como varchar(4000)) causaria uma conversão tipo (d).

Como exemplo final, dada uma consulta SELECT NULL + @p, int é escolhido para @p porque resulta numa conversão do tipo (c).

Permissions

Requer permissão para executar o argumento @tsql .

Examples

O exemplo seguinte devolve informações como o tipo de dado esperado para os parâmetros e @name não declarados@id.

EXECUTE sp_describe_undeclared_parameters @tsql = N'SELECT object_id, name, type_desc
FROM sys.indexes
WHERE object_id = @id OR name = @name';

Quando o @id parâmetro é fornecido como @params referência, o @id parâmetro é omitido do conjunto de resultados e apenas o @name parâmetro é descrito.

EXECUTE sp_describe_undeclared_parameters @tsql = N'SELECT object_id, name, type_desc
FROM sys.indexes
WHERE object_id = @id OR NAME = @name', @params = N'@id int';

Conteúdo relacionado

• sp_describe_first_result_set (Transact-SQL)
• sys.dm_exec_describe_first_result_set (Transact-SQL)
• sys.dm_exec_describe_first_result_set_for_object (Transact-SQL)