sp_describe_undeclared_parameters (Transact-SQL)

Статья
08/21/2024

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure конечную точку аналитики SQL Azure Synapse Analytics в хранилище Microsoft Fabric в Microsoft Fabric

Возвращает результирующий набор, содержащий метаданные о необъявленных параметрах в пакете Transact-SQL. Учитывает каждый параметр, используемый в пакете @tsql , но не объявленный в @params. Возвращается результирующий набор, содержащий одну строку для каждого такого параметра со сведениями о предполагаемом типе параметра. Процедура возвращает пустой результирующий набор, если входной пакет @tsql не имеет параметров, кроме объявленных в @params.

Соглашения о синтаксисе Transact-SQL

Синтаксис

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

Примечание.

Чтобы использовать эту хранимую процедуру в Azure Synapse Analytics в выделенном пуле SQL, задайте уровень 20 совместимости базы данных или более поздней версии. Чтобы отказаться, измените уровень 10совместимости базы данных на .

Аргументы

[ @tsql = ] 'tsql'

Одна или несколько инструкций Transact-SQL. @tsql может быть nvarchar(n) или nvarchar(max).

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

@params предоставляет строку объявления для параметров пакета Transact-SQL аналогично тому, как sp_executesql работает. @params может быть nvarchar(n) или nvarchar(max).

Строка, содержащая определения всех параметров, внедренных в @tsql. Строка должна представлять собой константу в Юникоде либо переменную в этом же формате. Определение каждого параметра состоит из имени параметра и типа данных. n — это заполнитель, указывающий дополнительные определения параметров. Если инструкция Transact-SQL или пакет в инструкции не содержит параметров, @params не требуется. Значение параметра по умолчанию — NULL.

Значения кода возврата

sp_describe_undeclared_parameters всегда возвращает состояние нулевого значения при успешном выполнении. Если процедура вызывает ошибку, а процедура вызывается как RPC, состояние возврата заполняется типом ошибки, как описано в столбце error_type sys.dm_exec_describe_first_result_set. Если процедура вызывается из Transact-SQL, возвращаемое значение всегда равно нулю, даже в случаях ошибок.

Результирующий набор

sp_describe_undeclared_parameters возвращает следующий результирующий набор.

Имя столбца	Тип данных	Description
`parameter_ordinal`	int	Содержит порядковый номер параметра в результирующем наборе. Позиция первого параметра указывается как `1`. Не допускает значения NULL.
`name`	sysname	Содержит имя параметра. Не допускает значения NULL.
`suggested_system_type_id`	int	Содержит `system_type_id` тип данных параметра, указанного в `sys.types`разделе . Для типов СРЕДЫ CLR, даже если `system_type_name` столбец возвращается `NULL`, этот столбец возвращает значение `240`. Не допускает значения NULL.
`suggested_system_type_name`	nvarchar(256)	Содержит имя типа данных. Включает аргументы (длина, точность, масштаб), заданные для типа данных параметра. Если тип данных является пользовательским псевдонимом, то здесь указывается базовый системный тип данных. Если это определяемый пользователем тип данных CLR, `NULL` возвращается в этом столбце. Если тип параметра не может быть выведен, `NULL` возвращается. Допускает значение NULL.
`suggested_max_length`	smallint	См. раздел `sys.columns`. для `max_length` описания столбца. Не допускает значения NULL.
`suggested_precision`	tinyint	См. раздел `sys.columns`. содержащий описание столбца precision. Не допускает значения NULL.
`suggested_scale`	tinyint	См. раздел `sys.columns`. содержащий описание столбца scale. Не допускает значения NULL.
`suggested_user_type_id`	int	Для типов СРЕДЫ CLR и псевдонимов `user_type_id` содержит тип данных столбца, как указано в `sys.types`. В противном случае — `NULL`. Допускает значение NULL.
`suggested_user_type_database`	sysname	Для типов CLR и псевдонимов содержит имя базы данных, в которой этот тип определен. В противном случае — `NULL`. Допускает значение NULL.
`suggested_user_type_schema`	sysname	Для типов CLR и псевдонимов содержит имя схемы, в которой этот тип определен. В противном случае — `NULL`. Допускает значение NULL.
`suggested_user_type_name`	sysname	Для типов CLR и псевдонимов содержит имя типа. В противном случае — `NULL`.
`suggested_assembly_qualified_type_name`	nvarchar(4000)	Для типов CLR возвращает имя сборки и класса, определяющего тип. В противном случае — `NULL`. Допускает значение NULL.
`suggested_xml_collection_id`	int	Содержит `xml_collection_id` тип данных параметра, указанного в `sys.columns`разделе . Этот столбец возвращается `NULL` , если возвращаемый тип не связан с коллекцией схем XML. Допускает значение NULL.
`suggested_xml_collection_database`	sysname	Содержит базу данных, в которой определена коллекция схем XML, связанная с этим типом. Этот столбец возвращается `NULL` , если возвращаемый тип не связан с коллекцией схем XML. Допускает значение NULL.
`suggested_xml_collection_schema`	sysname	Содержит схему, в которой определена коллекция схем XML, связанная с этим типом. Этот столбец возвращается `NULL` , если возвращаемый тип не связан с коллекцией схем XML. Допускает значение NULL.
`suggested_xml_collection_name`	sysname	Содержит имя коллекции схем XML, связанной с этим типом. Этот столбец возвращается `NULL` , если возвращаемый тип не связан с коллекцией схем XML. Допускает значение NULL.
`suggested_is_xml_document`	bit	Возвращает, `1` если возвращаемый тип является XML- и этот тип гарантированно является XML-документом. В противном случае возвращает значение `0`. Не допускает значения NULL.
`suggested_is_case_sensitive`	bit	Возвращает значение `1` , если столбец относится к типу строки с учетом регистра, и `0` если это не так. Не допускает значения NULL.
`suggested_is_fixed_length_clr_type`	bit	Возвращает значение `1` , если столбец имеет тип среды CLR фиксированной длины и `0` если это не так. Не допускает значения NULL.
`suggested_is_input`	bit	Возвращает, `1` если параметр используется в любом месте, отличном от левой стороны назначения. В противном случае возвращает значение `0`. Не допускает значения NULL.
`suggested_is_output`	bit	Возвращает, `1` если параметр используется слева от назначения или передается в выходной параметр хранимой процедуры. В противном случае возвращает значение `0`. Не допускает значения NULL.
`formal_parameter_name`	sysname	Если параметр служит аргументом хранимой процедуры или определяемой пользователем функции, здесь возвращается имя соответствующего формального параметра. В противном случае возвращает значение `NULL`. Допускает значение NULL.
`suggested_tds_type_id`	int	Для внутреннего использования. Не допускает значения NULL.
`suggested_tds_length`	int	Для внутреннего использования. Не допускает значения NULL.

Замечания

sp_describe_undeclared_parameters всегда возвращает состояние нуля.

Наиболее распространенное использование заключается в том, что приложение получает инструкцию Transact-SQL, которая может содержать параметры и должна обрабатывать их каким-то образом. Примером является пользовательский интерфейс (например ODBCTest , или RowsetViewer) где пользователь предоставляет запрос с синтаксисом параметров ODBC. Приложение должно динамически обнаруживать число параметров и запрашивать каждый параметр у пользователя.

Другим примером служит ситуация, где пользователь не вводит данные, а приложение должно просматривать параметры и получать для них данные из другого расположения (из таблицы и т. п.) В этом случае приложению не нужно одновременно передавать все сведения о параметрах. Вместо этого приложение может получить сведения обо всех параметрах от поставщика, а сами данные получить из таблицы. Использование sp_describe_undeclared_parameters кода является более универсальным и, скорее всего, требует изменения, если структура данных изменится позже.

sp_describe_undeclared_parameters возвращает ошибку в любом из следующих случаев.

Входные @tsql не является допустимым пакетом Transact-SQL. Допустимость определяется анализом и анализом пакета Transact-SQL. Любые ошибки, вызванные пакетом во время оптимизации запросов или во время выполнения, не учитываются при определении допустимости пакета Transact-SQL.
@params не NULL является и содержит строку, которая не является синтаксически допустимой строкой объявления для параметров или содержит строку, которая объявляет любой параметр несколько раз.
Входной пакет Transact-SQL объявляет локальную переменную того же имени, что и параметр, объявленный в @params.
Инструкция ссылается на временные таблицы.
В запрос включено создание постоянной таблицы, к которой он будет обращен.

Если @tsql не имеет параметров, кроме параметров, объявленных в @params, процедура возвращает пустой результирующий набор.

Примечание.

Переменную необходимо объявить скалярной переменной Transact-SQL или появиться ошибка.

Алгоритм выбора параметров

Для запроса с необъявленными параметрами выполняется процесс определения типов данных необъявленных параметров, состоящий из трех шагов.

Шаг 1. Поиск типов данных вложенных выражений

Первым шагом в вычете типов данных для запроса с необъявленными параметрами является поиск типов данных всех вложенных выражений, типы данных которых не зависят от необъявленных параметров. Тип можно определить для следующих выражений:

столбцы, константы, переменные и объявленные параметры;
результаты вызова определяемой пользователем функции;
Выражение с типами данных, которые не зависят от необъявленных параметров для всех входных данных.

В качестве примера рассмотрим запрос SELECT dbo.tbl(@p1) + c1 FROM t1 WHERE c2 = @p2 + 2. Выражения dbo.tbl(@p1) + c1 и типы данных, c2 а также выражения @p1 и @p2 + 2 не имеют.

Если после этого шага любое выражение (кроме вызова определяемой пользователем функции) содержит два аргумента без типов данных, то определение типов завершается с ошибкой. Например, все следующие инструкции вызывают ошибки:

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

В следующем примере ошибка не возникает:

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

Шаг 2. Поиск самых внутренних выражений

Для заданного необъявленного параметра @pалгоритм вычета типов находит самое внутреннее выражение E(@p) , содержащее @p и одно из следующих аргументов:

аргументом оператора сравнения или присваивания;
аргументом определяемой пользователем функции (в том числе определяемой пользователем функции, возвращающей табличное значение), процедуры или метода;
Аргумент VALUES предложения инструкции INSERT .
Аргумент для или CAST CONVERT.

Алгоритм вычета типов находит целевой тип TT(@p) данных для E(@p). Далее показаны целевые типы данных для предыдущих примеров:

тип данных на другой стороне сравнения или присваивания;
объявленный тип данных параметра, в который передается этот аргумент;
тип данных столбца, в который вставляется это значение;
тип данных, к которому приводится или преобразуется инструкция.

В качестве примера рассмотрим запрос SELECT * FROM t WHERE @p1 = dbo.tbl(@p2 + c1). Затем E(@p1) = @p1, E(@p2) = @p2 + c1TT(@p1) является объявленным возвращаемым типом dbo.tblданных и TT(@p2) является объявленным типом данных параметра для dbo.tbl.

Если @p он не содержится в любом выражении, указанном в начале шага 2, алгоритм вычета типов определяет, что E(@p) является крупнейшим скалярным выражением, которое содержит @p, и алгоритм вычета типов не вычисляет целевой тип TT(@p) данных для E(@p). Например, если запрос выполняется SELECT @p + 2 , E(@p) = @p + 2и нет TT(@p).

Шаг 3. Вывод типов данных

Теперь, когда E(@p) идентифицируется TT(@p) , алгоритм вычета типов выводит тип данных для @p одного из следующих двух способов:

Простое определение

Если E(@p) = @p и TT(@p) существует, то есть, если @p это прямой аргумент к одному из выражений, перечисленных в начале шага 2, алгоритм вычета типов выводит тип @p данных.TT(@p) Например:
```
SELECT * FROM t WHERE c1 = @p1 AND @p2 = dbo.tbl(@p3);
```
Тип данных для @p1, @p2и @p3 будет типом c1данных , возвращаемым типом dbo.tblданных и типом dbo.tbl данных параметра соответственно.

В качестве специального случая, если @p аргумент является аргументом к <оператору >, <=или >= оператору, простые правила вычета не применяются. Алгоритм определения типов будет использовать общие правила определения, описанные в следующем разделе. Например, если c1 это столбец типа данных char(30),рассмотрите следующие два запроса:
```
SELECT * FROM t WHERE c1 = @p;
SELECT * FROM t WHERE c1 > @p;
```
В первом случае алгоритм вычета типов выводит char(30) в качестве типа данных в @p соответствии с правилами, приведенными выше в этой статье. Во втором случае алгоритм вычета типов выводит varchar(8000) в соответствии с общими правилами вычета в следующем разделе.
Общее определение

Если простое вычет не применяется, для необъявленных параметров считаются следующие типы данных:
- Целые типы данных (bit, tinyint, smallint, int, bigint)
- Типы данных денег (smallmoney, money)
- Типы данных с плавающей запятой (float, real)
- numeric(38, 19) — другие числовые или десятичные типы данных не считаются.
- varchar(8000), varchar(max), nvarchar(4000)и nvarchar(max) — другие строковые типы данных (например, text, char(8000), nvarchar(30)и т. д.) не считаются.
- varbinary(8000) и varbinary(max) — другие двоичные типы данных не считаются (например , image, binary(8000), varbinary(30)и т. д.).
- date, time(7), smalldatetime, datetime, datetime2(7), datetimeoffset(7) — другие типы даты и времени, такие как time(4), не считаются.
- sql_variant
- xml
- Системные типы среды CLR (hierarchyid, geometry, geography)
- Определяемые пользователем типы CLR

Условия выбора

Любой тип данных-кандидат, который нарушает допустимость запроса, отклоняется. Из оставшихся кандидатов алгоритм определения типов выбирает один тип данных по следующим правилам.

Выбран тип данных, который создает наименьшее количество неявных E(@p) преобразований. Если определенный тип данных создает тип данных, E(@p) отличный от TT(@p)этого, алгоритм вычета типов считает это дополнительным неявным преобразованием из типа E(@p) данных в TT(@p).

Например:
```
SELECT * FROM t WHERE Col_Int = Col_Int + @p;
```
В этом случае E(@p) это и TT(@p) есть Col_Int + @p int.Int выбирается, @p так как он не создает неявных преобразований. Любой другой выбор типа данных требует не меньше одного неявного преобразования.
Если несколько типов данных имеют минимальное число преобразований, то используется тип данных с максимальным приоритетом. Например:
```
SELECT * FROM t WHERE Col_Int = Col_smallint + @p;
```
В этом случае int и smallint создают одно преобразование. Для любого другого типа данных требуется несколько преобразований. Так как int имеет приоритет над smallint, int используется для @p. Дополнительные сведения о приоритете типов данных см. в разделе "Приоритет типа данных".

Это правило применяется только при неявном преобразовании между каждым типом данных, который связывается в соответствии с правилом 1 и типом данных с наибольшим приоритетом. Если неявное преобразование отсутствует, вычет типов данных завершается ошибкой. Например, в запросе SELECT @p FROM tвычет типов данных завершается сбоем, так как любой тип @p данных будет одинаково хорошим. Например, нет неявного преобразования из int в xml.
Если два аналогичных типа данных привязаны к правилу 1, например varchar(8000) и varchar(max),то выбирается меньший тип данных (varchar(8000). Тот же принцип применяется к типам данных nvarchar и varbinary .
В рамках правила 1 алгоритм определения типов используют различные приоритеты преобразований. Далее показаны преобразования в порядке убывания приоритета.
1. Преобразование между типами с одним базовым типом, имеющими разную длину.
2. Преобразование между фиксированной длиной и переменной длиной версии одинаковых типов данных (например, char в varchar).
3. Преобразование между NULL и int.
4. Все прочие преобразования.

Например, для запроса SELECT * FROM t WHERE [Col_varchar(30)] > @pвыбирается varchar(8000), так как преобразование (a) лучше всего. Для запроса SELECT * FROM t WHERE [Col_char(30)] > @pvarchar(8000) по-прежнему выбран, так как он вызывает преобразование типа (b), а другой выбор (например, varchar(4000)) приведет к преобразованию типа (d).

В качестве последнего примера, учитывая запрос SELECT NULL + @p, int выбирается @p , так как он приводит к преобразованию типа (c).

Разрешения

Требуется разрешение на выполнение аргумента @tsql .

Примеры

В следующем примере возвращаются такие данные, как ожидаемый тип данных для необъявленных параметров @id и @name.

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

Если параметр @id передается по ссылке @params, то параметр @id исключается из результирующего набора и описывается только параметр @name.

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

Поделиться через

sp_describe_undeclared_parameters (Transact-SQL)

Синтаксис

Аргументы

[ @tsql = ] 'tsql'

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

Значения кода возврата

Результирующий набор

Замечания

Алгоритм выбора параметров

Шаг 1. Поиск типов данных вложенных выражений

Шаг 2. Поиск самых внутренних выражений

Шаг 3. Вывод типов данных

Условия выбора

Разрешения

Примеры

Обратная связь

Дополнительные ресурсы

Поделиться через

sp_describe_undeclared_parameters (Transact-SQL)

Синтаксис

Аргументы

[ @tsql = ] 'tsql'

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

Значения кода возврата

Результирующий набор

Замечания

Алгоритм выбора параметров

Шаг 1. Поиск типов данных вложенных выражений

Шаг 2. Поиск самых внутренних выражений

Шаг 3. Вывод типов данных

Условия выбора

Разрешения

Примеры

Связанный контент

Обратная связь

Дополнительные ресурсы