sp_describe_undeclared_parameters (Transact-SQL)

Artigo
08/21/2024

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure Azure Synapse Analytics Ponto de extremidade de análises SQL no Microsoft Fabric Warehouse no Microsoft Fabric

Retorna um conjunto de resultados que contém metadados sobre 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 que contém uma linha para cada um desses parâmetros é retornado com as informações de tipo deduzidas para esse parâmetro. O procedimento retornará um conjunto de resultados vazios se o lote de entrada @tsql não tiver parâmetros, exceto aqueles declarados em @params.

Convenções de sintaxe de Transact-SQL

Sintaxe

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

Observação

Para usar esse procedimento armazenado no Azure Synapse Analytics em um pool de SQL dedicado, defina o nível de compatibilidade do banco de dados como 20 ou superior. Para recusar, altere o nível de compatibilidade do banco de dados para 10.

Argumentos

@tsql [ = ] 'tsql'

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

@params [ = ] N'@parameter_name data_type [ ,... n ]'

@params fornece uma cadeia de caracteres de declaração para parâmetros para o lote Transact-SQL, da mesma forma que funcionasp_executesql. @params pode ser nvarchar(n) ou nvarchar(max).

Uma cadeia de caracteres que contém as definições de todos os parâmetros inseridos no @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âmetro adicionais. Se a instrução Transact-SQL ou o 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 do código de retorno

sp_describe_undeclared_parameters Sempre retorna o status zero em caso de êxito. Se o procedimento gerar um erro e o procedimento for chamado como RPC, o status de retorno será preenchido pelo tipo de erro, conforme descrito na error_type coluna .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.

Conjunto de resultados

sp_describe_undeclared_parameters retorna o conjunto de resultados a seguir.

Nome da coluna	Tipo de dados	Descrição
`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 permite valor nulo.
`name`	sysname	Contém o nome do parâmetro. Não permite valor nulo.
`suggested_system_type_id`	int	Contém o `system_type_id` tipo de dados do parâmetro, conforme especificado em `sys.types`. Para tipos CLR, mesmo que a `system_type_name` coluna retorne `NULL`, essa coluna retorna o valor `240`. Não permite valor nulo.
`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 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 nesta coluna. Se o tipo do parâmetro não puder ser deduzido, `NULL` será retornado. Anulável.
`suggested_max_length`	smallint	Consulte `sys.columns`. para descrição da `max_length` coluna. Não permite valor nulo.
`suggested_precision`	tinyint	Consulte `sys.columns`. para obter a descrição da coluna de precisão. Não permite valor nulo.
`suggested_scale`	tinyint	Consulte `sys.columns`. para obter a descrição da coluna de escala. Não permite valor nulo.
`suggested_user_type_id`	int	Para tipos CLR e alias, contém o `user_type_id` tipo de dados da coluna, conforme especificado em `sys.types`. Caso contrário, é `NULL`. Anulável.
`suggested_user_type_database`	sysname	Para tipos de CLR e de alias, contém o nome do banco de dados no qual o tipo é definido. Caso contrário, é `NULL`. Anulável.
`suggested_user_type_schema`	sysname	Para tipos de CLR e de alias, contém o nome do esquema no qual o tipo é definido. Caso contrário, é `NULL`. Anulável.
`suggested_user_type_name`	sysname	Para tipos de CLR e de alias, contém o nome do tipo. Caso contrário, é `NULL`.
`suggested_assembly_qualified_type_name`	nvarchar(4000)	Para tipos de CLR, retorna o nome do assembly e da classe que define o tipo. Caso contrário, é `NULL`. Anulável.
`suggested_xml_collection_id`	int	Contém o `xml_collection_id` tipo de dados do parâmetro, conforme especificado em `sys.columns`. Essa coluna retornará `NULL` se o tipo retornado não estiver associado a uma coleção de esquemas XML. Anulável.
`suggested_xml_collection_database`	sysname	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. Anulável.
`suggested_xml_collection_schema`	sysname	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. Anulável.
`suggested_xml_collection_name`	sysname	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. Anulável.
`suggested_is_xml_document`	bit	Retorna `1` se o tipo que está sendo retornado for XML e esse tipo tiver a garantia de ser um documento XML. Caso contrário, retornará `0`. Não permite valor nulo.
`suggested_is_case_sensitive`	bit	Retorna `1` se a coluna for de um tipo de cadeia de caracteres que diferencia maiúsculas de minúsculas e `0` se não for. Não permite valor nulo.
`suggested_is_fixed_length_clr_type`	bit	Retorna `1` se a coluna for de um tipo CLR de comprimento fixo e `0` se não for. Não permite valor nulo.
`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, retornará `0`. Não permite valor nulo.
`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, retornará `0`. Não permite valor nulo.
`formal_parameter_name`	sysname	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, retornará `NULL`. Anulável.
`suggested_tds_type_id`	int	Para uso interno. Não permite valor nulo.
`suggested_tds_length`	int	Para uso interno. Não permite valor nulo.

Comentários

sp_describe_undeclared_parameters sempre retorna o status 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) em que 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 uso de sp_describe_undeclared_parameters código é mais genérico e é menos provável que exija modificação se a estrutura de dados for alterada posteriormente.

sp_describe_undeclared_parameters retorna um erro em qualquer um dos casos a seguir.

O @tsql de entrada não é um lote Transact-SQL válido. A validade é determinada pela análise do lote do Transact-SQL. 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 de caracteres que não é uma cadeia de caracteres de declaração sintaticamente válida para parâmetros ou se contém uma cadeia de caracteres que declara qualquer parâmetro mais de uma vez.
O lote Transact-SQL de entrada declara uma variável local com o mesmo nome que um parâmetro declarado em @params.
A instrução faz referência a tabelas temporárias.
A consulta inclui a criação de uma tabela permanente que é então consultada.

Se @tsql não tiver parâmetros, além dos parâmetros declarados em @params, o procedimento retornará um conjunto de resultados vazio.

Observação

Você deve declarar a variável como uma variável Transact-SQL escalar ou um erro será exibido.

Algoritmo de seleção de parâmetros

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: Localizar os tipos de dados das subexpressões

A primeira etapa na dedução de 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 dos 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 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.

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 exemplo a seguir não produz um erro:

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

Etapa 2: Localizar expressões mais internas

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

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 VALUES cláusula de uma INSERT declaração.
Um argumento para um CAST ou CONVERT.

O algoritmo de dedução de tipo localiza um tipo TT(@p) de dados de destino 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). Então E(@p1) = @p1, E(@p2) = @p2 + c1, TT(@p1) é o tipo de dados de retorno declarado de , e TT(@p2) é o tipo de dados de parâmetro declarado dbo.tblpara 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 TT(@p) de dados de destino para E(@p). Por exemplo, se a consulta for SELECT @p + 2 então E(@p) = @p + 2, e não houver .TT(@p)

Etapa 3: Deduzir tipos de dados

Agora que E(@p) e TT(@p) são identificados, o algoritmo de dedução de tipo deduz um tipo de dados para @p de uma das duas maneiras a seguir:

Dedução simples

Se E(@p) = @p e TT(@p) existir, 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 ser TT(@p). Por exemplo:
```
SELECT * FROM t WHERE c1 = @p1 AND @p2 = dbo.tbl(@p3);
```
O tipo de dados para @p1, , e @p3 será o tipo de dados de c1, o tipo de dados de retorno de , e o tipo de dbo.tbldados de parâmetro paradbo.tbl, @p2respectivamente.

Como um caso especial, se @p for um argumento para um <operador , >, <=, ou >= , regras de dedução simples não se aplicam. 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 de @p acordo com as regras anteriores neste artigo. 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 se aplicar, 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 de moeda (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 (tais 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 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.

O tipo de dados que produz o menor número de conversões implícitas é E(@p) selecionado. Se um determinado tipo de dados produzir um tipo de dados diferente E(@p) de TT(@p), o algoritmo de dedução de tipo considerará isso 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;
```
Nesse caso, E(@p) is Col_Int + @p e TT(@p) is int. int é escolhido porque @p não produz conversões implícitas. Qualquer outra escolha de tipo de dados gera uma conversão implícita pelo menos.
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;
```
Neste caso, int e smallint produzem 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 precedência de tipo de dados, consulte Precedência de tipo de dados.

Essa regra só se aplica se houver uma conversão implícita entre cada tipo de dados vinculado à regra 1 e o tipo de dados com maior precedência. Se não houver conversão implícita, a dedução do tipo de dados falhará com um erro. Por exemplo, na consulta SELECT @p FROM t, a dedução do tipo de dados falha porque qualquer tipo de dados for @p seria igualmente bom. Por exemplo, não há conversão implícita de int para xml.
Se houver dois tipos de dados semelhantes ligados de acordo com a regra 1, por exemplovarchar(8000) e varchar(max), o menor tipo de dados (varchar(8000)) será escolhido. O mesmo princípio se aplica aos tipos de dados nvarchar e varbinary.
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 para 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, tal como varchar(4000), geraria uma conversão de tipo (d).

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

Permissões

Requer 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.

EXEC 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.

EXEC 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';

Compartilhar via

sp_describe_undeclared_parameters (Transact-SQL)

Sintaxe

Argumentos

@tsql [ = ] 'tsql'

@params [ = ] N'@parameter_name data_type [ ,... n ]'

Valores do código de retorno

Conjunto de resultados

Comentários

Algoritmo de seleção de parâmetros

Etapa 1: Localizar os tipos de dados das subexpressões

Etapa 2: Localizar expressões mais internas

Etapa 3: Deduzir tipos de dados

Critérios de seleção

Permissões

Exemplos

Comentários

Recursos adicionais

Compartilhar via

sp_describe_undeclared_parameters (Transact-SQL)

Sintaxe

Argumentos

@tsql [ = ] 'tsql'

@params [ = ] N'@parameter_name data_type [ ,... n ]'

Valores do código de retorno

Conjunto de resultados

Comentários

Algoritmo de seleção de parâmetros

Etapa 1: Localizar os tipos de dados das subexpressões

Etapa 2: Localizar expressões mais internas

Etapa 3: Deduzir tipos de dados

Critérios de seleção

Permissões

Exemplos

Conteúdo relacionado

Comentários

Recursos adicionais