sp_describe_undeclared_parameters (Transact-SQL)
Aplica-se a:
SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureAzure Synapse AnalyticsPonto de extremidade de análises SQL no Microsoft FabricWarehouse 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'parameters' data type ] [, ...n]
Observação
Para usar esse procedimento armazenado no Azure Synapse Analytics no 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 = ] 'Transact-SQL\_batch' Uma ou mais instruções de 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. Os Parameters podem ser nvarchar(n) ou nvarchar(max).
É uma cadeia de caracteres que contém as definições de todos os parâmetros inseridos na instrução 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 do código de retorno
sp_describe_undeclared_parameters sempre retorna o status de retorno como zero em caso de ê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 | Confira sys.columns. para obter uma descrição da coluna max_length. |
| suggested_precision | tinyint NOT NULL | Confira sys.columns. para obter a descrição da coluna de precisão. |
| suggested_scale | tinyint NOT NULL | Confira sys.columns. para obter a descrição da coluna de escala. |
| suggested_user_type_id | int NULL | Para tipos de CLR e 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 retorno 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 tem que transmitir todas as informações de parâmetro de uma 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 válido do Transact-SQL. A validade é determinada pela análise do lote do Transact-SQL. Os erros causados pelo lote durante a otimização ou execução da consulta não serão considerados para determinar se o lote do 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 de entrada do Transact-SQL declarar uma variável local com o mesmo nome de um parâmetro declarado em @params.
Se a instrução fizer referência a qualquer tabela temporária.
A consulta inclui a criação de uma tabela permanente que é então consultada.
Se @tsql não tiver parâmetros, a não ser aqueles 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â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 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, então 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 > @pNo 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 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 serão considerados.
varbinary(8000) e varbinary(max) – outros tipos de dados binários não são considerados (tais 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 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 + @pNesse caso, E(@p) é Col_Int + @p e TT(@p) é int. int foi escolhido para @p porque 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 + @pNeste 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 em @p. Para obter mais informações sobre precedência de tipo de dados, confira 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.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:
Conversão entre o mesmo tipo de dados básico de comprimento diferente.
A conversão entre a versão de comprimento fixo e de comprimento variável dos mesmos tipos de dados (por exemplo, char em varchar).
Conversão entre NULL e int.
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 para @p porque resulta em uma conversão de tipo (c).
Permissões
Exige 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
Comentários
Em breve: Ao longo de 2024, eliminaremos os problemas do GitHub como o mecanismo de comentários para conteúdo e o substituiremos por um novo sistema de comentários. Para obter mais informações, consulte https://aka.ms/ContentUserFeedback.
Enviar e exibir comentários de