sp_describe_undeclared_parameters (Transact-SQL)

Retorna um conjunto de resultados que contém metadados sobre os parâmetros não declarados em um lote Transact-SQL. Considera cada parâmetro usado no lote @tsql, mas não declarado em @params. Um conjunto de resultados é retornado com uma linha para cada um desses parâmetros, com as informações de tipo deduzido desse parâmetro. O procedimento retornará um conjunto de resultados vazio se o lote de entrada **@tsql **não tiver parâmetros, exceto aqueles declarados em @params.

Convenções da sintaxe Transact-SQL

Sintaxe

sp_describe_undeclared_parameters 
    [ @tsql = ] 'Transact-SQL_batch' 
    [ , [ @params = ] N'parameters' data type ] [, ...n]

Argumentos

• [ @tsql = ] 'Transact-SQL_batch'
  Uma ou mais instruções Transact-SQL. Transact-SQL_batch pode ser nvarchar(n) ou nvarchar(max).

• [ @params = ] N'parameters'
  @params fornece uma cadeia de caracteres de declaração para os parâmetros do lote Transact-SQL, de modo semelhante ao funcionamento de sp_executesql. Parameters pode ser nvarchar(n) ou nvarchar(max).

  É uma cadeia de caracteres que contém as definições de todos os parâmetros inseridos em Transact-SQL_batch. 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âmetro adicionais. Se a instrução Transact-SQL ou lote na instrução não contiverem parâmetros, @params não será necessário. O valor padrão para este parâmetro é NULL.

• Datatype
  O tipo de dados do parâmetro.

Valores de código de retorno

sp_describe_undeclared_parameters sempre retorna o status de zero em êxito. Se o procedimento lançar um erro e se for chamado como uma RPC, o status de retorno será populado pelo tipo de erro descrito na coluna error_type de sys.dm_exec_describe_first_result_set. Se o procedimento for chamado de Transact-SQL, o valor de retorno sempre será zero, até mesmo em casos de erro.

Conjuntos de resultados

sp_describe_undeclared_parameters retorna o conjunto de resultados a seguir.

Nome da coluna	Tipo de dados	Descrição
parameter_ordinal	int NOT NULL	Contém a posição ordinal do parâmetro no conjunto de resultados. A posição do primeiro parâmetro será especificada como 1.
name	sysname NOT NULL	Contém o nome do parâmetro.
suggested_system_type_id	int NOT NULL	Contém o system_type_id do tipo de dados do parâmetro como especificado em sys.types. Para tipos de CLR, embora a coluna system_type_name retorne NULL, essa coluna retornará o valor 240.
suggested_system_type_name	nvarchar (256) NULL	Contém o nome do tipo de dados. Inclui argumentos (como comprimento, precisão, escala) especificados para o tipo de dados do parâmetro. Se o tipo de dados for um tipo de alias definido pelo usuário, o tipo de sistema subjacente será especificado aqui. Se for um tipo de dados CLR definido pelo usuário, NULL será retornado nessa coluna. Se não for possível deduzir o tipo de parâmetro, NULL será retornado.
suggested_max_length	smallint NOT NULL	Consulte sys.columns. para obter uma descrição da coluna max_length.
suggested_precision	tinyint NOT NULL	Consulte sys.columns. para obter a descrição da coluna de precisão.
suggested_scale	tinyint NOT NULL	Consulte sys.columns. para obter a descrição da coluna de escala.
suggested_user_type_id	int NULL	Para tipos de CLR e de alias, contém o user_type_id do tipo de dados da coluna como especificado em sys.types. Caso contrário, é NULL.
suggested_user_type_database	sysname NULL	Para tipos de CLR e de alias, contém o nome do banco de dados no qual o tipo é definido. Caso contrário, é NULL.
suggested_user_type_schema	sysname NULL	Para tipos de CLR e de alias, contém o nome do esquema no qual o tipo é definido. Caso contrário, é NULL.
suggested_user_type_name	sysname NULL	Para tipos de CLR e de alias, contém o nome do tipo. Caso contrário, é NULL.
suggested_assembly_qualified_type_name	nvarchar (4000) NULL	Para tipos de CLR, retorna o nome do assembly e da classe que define o tipo. Caso contrário, é NULL.
suggested_xml_collection_id	int NULL	Contém o xml_collection_id do tipo de dados do parâmetro como especificado em sys.columns. Essa coluna retornará NULL se o tipo retornado não estiver associado a uma coleção de esquemas XML.
suggested_xml_collection_database	sysname NULL	Contém o banco de dados no qual a coleção de esquemas XML associada a esse tipo está definida. Essa coluna retornará NULL se o tipo retornado não estiver associado a uma coleção de esquemas XML.
suggested_xml_collection_schema	sysname NULL	Contém o esquema no qual a coleção de esquemas XML associada a esse tipo está definida. Essa coluna retornará NULL se o tipo retornado não estiver associado a uma coleção de esquemas XML.
suggested_xml_collection_name	sysname NULL	Contém o nome da coleção de esquemas XML associada a esse tipo. Essa coluna retornará NULL se o tipo retornado não estiver associado a uma coleção de esquemas XML.
suggested_is_xml_document	bit NOT NULL	Retornará 1 se o tipo retornado for o XML e esse tipo for, com certeza, um documento XML. Caso contrário, retorna 0.
suggested_is_case_sensitive	bit NOT NULL	Retornará 1 se a coluna for de um tipo de cadeia de caracteres com diferenciação de maiúsculas e minúsculas e 0 se não for.
suggested_is_fixed_length_clr_type	bit NOT NULL	Retornará 1 se a coluna for de um tipo de CLR de comprimento fixo e 0 se não for.
suggested_is_input	bit NOT NULL	Retornará 1 se o parâmetro for usado em qualquer lugar, sem ser o lado esquerdo de uma atribuição. Caso contrário, retorna 0.
suggested_is_output	bit NOT NULL	Retornará 1 se o parâmetro for usado no lado esquerdo de uma atribuição ou se for transmitido a um parâmetro de saída de um procedimento armazenado. Caso contrário, retorna 0.
formal_parameter_name	sysname NULL	Se o parâmetro for um argumento para um procedimento armazenado ou uma função definida pelo usuário, retornará o nome do parâmetro formal correspondente. Caso contrário, retorna NULL.
suggested_tds_type_id	int NOT NULL	Para uso interno.
suggested_tds_length	int NOT NULL	Para uso interno.

Comentários

sp_describe_undeclared_parameters sempre retorna o status de zero.

O uso mais comum é quando um aplicativo recebe uma instrução Transact-SQL que pode conter parâmetros e precisa processá-los de algum modo. Um exemplo é uma interface do usuário (como ODBCTest ou RowsetViewer) onde o usuário fornece uma consulta com sintaxe de parâmetro ODBC. O aplicativo deve descobrir o número de parâmetros dinamicamente e deve solicitar cada parâmetro ao usuário.

Outro exemplo ocorre quando, sem entrada do usuário, um aplicativo precisa executar um loop dos parâmetros e obter os dados para eles de algum outro local (como uma tabela). Nesse caso, o aplicativo não precisa passar todas as informações de parâmetro de uma só vez. Em vez disso, o aplicativo pode obter todas as informações de parâmetros do provedor e pode obter os próprios dados na tabela. O código que usar sp_describe_undeclared_parameters será mais genérico e apresentará menor probabilidade de requerer modificação se a estrutura de dados for alterada posteriormente.

sp_describe_undeclared_parameters retorna um erro em quaisquer dos casos a seguir.

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

• Se @params não for NULL e se contiver uma cadeia de caracteres que não seja uma cadeia de caracteres de declaração sintaticamente válida para parâmetros ou se contiver uma cadeia de caracteres que declare qualquer parâmetro mais de uma vez.

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

• Se a instrução criar qualquer tabela temporária.

Se @tsql não tiver parâmetros, a não ser aqueles declarados em @params, o procedimento retornará um conjunto de resultados vazio.

Algoritmo de seleção de parâmetro

Para uma consulta com parâmetros não declarados, a dedução de tipo de dados para parâmetros não declarados é realizada em três etapas.

Etapa 1

A primeira etapa na dedução do tipo de dados para uma consulta com parâmetros não declarados é localizar os tipos de dados de todas as subexpressões cujos tipos de dados não dependem de parâmetros não declarados. É possível determinar o tipo para as seguintes expressões:

• Colunas, constantes, variáveis e parâmetros declarados.

• Os resultados de uma chamada para uma UDF (função definida pelo usuário).

• Uma expressão com tipos de dados que não dependem de 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 a expressão @p1 e @p2 + 2 não têm.

Depois dessa etapa, se qualquer expressão (sem ser uma chamada para uma UDF) tiver dois argumentos sem tipos de dados, a dedução de tipo falhará com um erro. Por exemplo, os seguintes itens geram erros:

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

O seguinte exemplo não gera um erro:

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

Etapa 2

Para um determinado parâmetro não declarado @p, o algoritmo de dedução de tipo localiza a expressão interna E(@p) que contém @p e é um dos seguintes:

• Um argumento para um operador comparação ou atribuição.

• Um argumento para uma função definida pelo usuário (incluindo a UDF com valor de tabela), procedimento ou método.

• Um argumento para uma cláusula VALUES de uma instrução INSERT.

• Um argumento para um CAST ou CONVERT.

O algoritmo de dedução de tipo localiza um tipo de dados de destino TT(@p) para E(@p). A seguir são apresentados tipos de dados de destino dos exemplos anteriores:

• O tipo de dados do outro lado da comparação ou atribuição.

• O tipo de dados declarado do parâmetro ao qual esse argumento foi passado.

• O tipo de dados da coluna na qual esse valor é inserido.

• O tipo de dados no qual a instrução está convertendo.

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

Se @p não estiver contido em nenhuma expressão listada no início da etapa 2, o algoritmo de dedução de tipo determinará que E(@p) é a maior expressão escalar que contém @p, e o algoritmo de dedução de tipo não calculará um tipo de dados de destino TT(@p) para E(@p). Por exemplo, se a consulta for SELECT @p + 2, E(@p) = @p + 2 e não haverá nenhum TT(@p).

Etapa 3

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

• Dedução simples

  Se E(@p) = @p e TT(@p) existirem, ou seja, se @p for diretamente um argumento para uma das expressões listadas no início da etapa 2, o algoritmo de dedução de tipo deduz o tipo de dados de @p como TT(@p). Por exemplo:

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

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

  Como um caso especial, se @p for um argumento para um operador <, >, <= ou >=, as regras de dedução simples não se aplicarão. O algoritmo de dedução de tipo usará as regras de dedução gerais explicadas na próxima seção. Por exemplo, se c1 for uma coluna do tipo de dados char(30), considere as duas consultas a seguir:

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

  No primeiro caso, o algoritmo de dedução de tipo deduz char(30) como o tipo de dados para @p, de acordo com as regras anteriores neste tópico. No segundo caso, o algoritmo de dedução de tipo deduz varchar(8000) de acordo com as regras de dedução geral na próxima seção.

• Dedução geral

  Se a dedução simples não for aplicável, os seguintes tipos de dados serão considerados para parâmetros não declarados:

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

  • Tipos de dados money (smallmoney, money)

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

  • numeric(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 de caracteres (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.).

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

  • sql_variant

  • xml

  • Tipos definidos pelo sistema de CLR (hierarchyid, geometry, geography)

  • Tipos definidos pelo usuário de CLR

Critérios de seleção

Dos tipos de dados candidatos, qualquer tipo de dados que invalide a consulta será rejeitado. Dos tipos de dados candidatos restantes, o algoritmo de dedução de tipo seleciona um item de acordo com as regras a seguir.

1. O tipo de dados que gera o menor número de conversões implícitas em E(@p) é selecionado. Se um determinado tipo de dados gerar um tipo de dados para E(@p) que seja diferente de TT(@p), o algoritmo de dedução de tipo considerará isso uma conversão implícita extra do tipo de dados de E(@p) em TT(@p).

   Por exemplo:

   SELECT * FROM t WHERE Col_Int = Col_Int + @p
   

   Nesse caso, E (@ p) é Col_Int + @ p e TT (@ p) é int. int é escolhido para @p porque não gera nenhuma conversão implícita. Qualquer outra escolha de tipo de dados gera uma conversão implícita pelo menos.

2. Se houver vários tipos de dados ligados ao menor número de conversões, o tipo de dados com a precedência mais alta será usada. Por exemplo

   SELECT * FROM t WHERE Col_Int = Col_smallint + @p
   

   Nesse caso, int e smallint geram uma conversão. Todos os outros tipos de dados geram mais de uma conversão. Como int tem precedência sobre smallint, int é usado para @p. Para obter mais informações sobre a precedência de tipos de dados, consulte Precedência de tipo de dados (Transact-SQL).

   Essa regra somente se aplicará se houver uma conversão implícita entre cada tipo de dados ligado de acordo com regra 1 e o tipo de dados com a precedência mais alta. Se não houver nenhuma conversão implícita, a dedução de tipo de dados falhará com um erro. Por exemplo, na consulta SELECT @p FROM t, a dedução de tipo de dados falhará porque qualquer tipo de dados para @p seria igualmente bom. Por exemplo, não há nenhuma conversão implícita de int em xml.

3. Se houver dois tipos de dados semelhantes ligados de acordo com a regra 1, por exemplo varchar(8000) e varchar(max), o tipo de dados menor (varchar(8000)) será escolhido. O mesmo princípio se aplica aos tipos de dados nvarchar e varbinary.

4. Para os propósitos da regra 1, o algoritmo de dedução de tipo considera certas conversões melhores que outras. As conversões, na ordem da melhor para a pior são:

   1. Conversão entre o mesmo tipo de dados básico de comprimento diferente.

   2. Conversão entre a versão de comprimento fixo e de comprimento variável dos mesmos tipos de dados (por exemplo, char em varchar).

   3. Conversão entre NULL e int.

   4. Qualquer outra conversão.

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

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

Permissões

Exige a permissão para executar o argumento @tsql.

Exemplos

O exemplo a seguir retorna informações como o tipo de dados esperado para os parâmetros não declarados @id e @name.

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

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

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'

Consulte também

Referência

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)