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

sp_describe_undeclared_parameters (Transact-SQL)

  • Статья

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

Значок ссылки на раздел Синтаксические обозначения в Transact-SQL

Синтаксис

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

Аргументы

  • [ @tsql = ] 'Transact-SQL_batch'
    Одна или несколько инструкций Transact-SQL. Transact-SQL_batch может иметь тип nvarchar(n) или nvarchar(max).

  • [ @params = ] N'parameters'
    Аргумент @params содержит строку объявления для параметров в пакете Transact-SQL аналогично хранимой процедуре sp_executesql. Parameters может иметь тип nvarchar(n) или nvarchar(max).

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

  • Datatype
    Тип данных параметра.

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

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

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

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

Имя столбца

Тип данных

Описание

parameter_ordinal

int NOT NULL

Содержит порядковый номер параметра в результирующем наборе. Позиция первого параметра будет указана как 1.

name

sysname NOT NULL

Содержит имя параметра.

suggested_system_type_id

int NOT NULL

Содержит system_type_id для типа данных параметра, как указано в sys.types.

Для типов CLR, даже если system_type_name возвращает NULL, этот столбец вернет значение 240.

suggested_system_type_name

nvarchar (256) NULL

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

suggested_max_length

smallint NOT NULL

См. раздел sys.columns, содержащий описание столбца max_length.

suggested_precision

tinyint NOT NULL

См. раздел sys.columns, содержащий описание столбца precision.

suggested_scale

tinyint NOT NULL

См. раздел sys.columns, содержащий описание столбца scale.

suggested_user_type_id

int NULL

Для типов CLR и псевдонимов содержит user_type_id для типа данных столбца, как указано в sys.types. В противном случае значение равно NULL.

suggested_user_type_database

sysname NULL

Для типов CLR и псевдонимов содержит имя базы данных, в которой этот тип определен. В противном случае значение равно NULL.

suggested_user_type_schema

sysname NULL

Для типов CLR и псевдонимов содержит имя схемы, в которой этот тип определен. В противном случае значение равно NULL.

suggested_user_type_name

sysname NULL

Для типов CLR и псевдонимов содержит имя типа. В противном случае значение равно NULL.

suggested_assembly_qualified_type_name

nvarchar (4000) NULL

Для типов CLR возвращает имя сборки и класса, определяющего тип. В противном случае значение равно NULL.

suggested_xml_collection_id

int NULL

Содержит xml_collection_id для типа данных параметра, как указано в sys.columns. Этот столбец возвратит NULL, если возвращаемый тип не связан с коллекцией схем XML.

suggested_xml_collection_database

sysname NULL

Содержит базу данных, в которой определена коллекция схем XML, связанная с этим типом. Этот столбец возвратит NULL, если возвращаемый тип не связан с коллекцией схем XML.

suggested_xml_collection_schema

sysname NULL

Содержит схему, в которой определена коллекция схем XML, связанная с этим типом. Этот столбец возвратит NULL, если возвращаемый тип не связан с коллекцией схем XML.

suggested_xml_collection_name

sysname NULL

Содержит имя коллекции схем XML, связанной с этим типом. Этот столбец возвратит NULL, если возвращаемый тип не связан с коллекцией схем XML.

suggested_is_xml_document

bit NOT NULL

Возвращает значение 1, если возвращается тип XML и этот тип гарантированно представляет собой XML-документ. В противном случае возвращается 0.

suggested_is_case_sensitive

bit NOT NULL

Возвращает значение 1, если столбец относится к строковому типу с учетом регистра, либо значение 0 в противном случае.

suggested_is_fixed_length_clr_type

bit NOT NULL

Возвращает значение 1, если столбец относится к типу CLR с фиксированной длиной, либо значение 0 в противном случае.

suggested_is_input

bit NOT NULL

Возвращает значение 1, если параметр используется за пределами левой стороны присваивания. В противном случае возвращается 0.

suggested_is_output

bit NOT NULL

Возвращает значение 1, если параметр используется в левой стороне присваивания или передается в выходной параметр хранимой процедуры. В противном случае возвращается 0.

formal_parameter_name

sysname NULL

Если параметр служит аргументом хранимой процедуры или определяемой пользователем функции, здесь возвращается имя соответствующего формального параметра. В противном случае возвращается NULL.

suggested_tds_type_id

int NOT NULL

Для внутреннего использования.

suggested_tds_length

int NOT NULL

Для внутреннего использования.

Замечания

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

Чаще всего она применяется, когда приложению передается инструкция 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, то процедура возвращает пустой результирующий набор.

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

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

Шаг 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 + c1, TT(@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) по общим правилам определения, описанным в следующем разделе.

  • Общее определение

    Если простое определение не действует, то для необъявленных параметров рассматриваются следующие типы данных.

    • Типы данных integer (bit, tinyint, smallint, int, bigint)

    • Типы данных money (smallmoney, money)

    • Типы данных с плавающей запятой (float, real)

    • numeric(38, 19). Другие числовые типы и типы данных demical не рассматриваются.

    • 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

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

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

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

    Например:

    SELECT * FROM t WHERE Col_Int = Col_Int + @p

    В данном случае E(@p) имеет тип Col_Int + @p, а TT(@p) имеет тип int. Для @p выбирается тип int, поскольку он не требует неявных преобразований. Любой другой выбор типа данных требует не меньше одного неявного преобразования.

  2. Если несколько типов данных имеют минимальное число преобразований, то используется тип данных с максимальным приоритетом. Например.

    SELECT * FROM t WHERE Col_Int = Col_smallint + @p

    В этом случае для типов int и smallint требуется одно преобразование. Для любого другого типа данных требуется несколько преобразований. Поскольку int имеет приоритет выше smallint, то для @p используется тип int. Дополнительные сведения о приоритете типов данных см. в разделе Приоритет типов данных (Transact-SQL).

    Это правило применяется, только если существует неявное преобразование между каждым из типов, равнозначных по правилу 1, и типом данных с максимальным приоритетом. Если неявное преобразование отсутствует, то определение типа данных завершается с ошибкой. Например, в запросе SELECT @p FROM t определение типа данных завершается ошибкой, поскольку все типы данных будут в равной степени соответствовать @p. Например, отсутствует неявное преобразование из int в xml.

  3. Если по правилу 1 равнозначными оказываются два схожих типа данных, например varchar(8000) и varchar(max), то выбирается тип данных с меньшим размером (varchar(8000)). То же правило применяется к типам nvarchar и varbinary.

  4. В рамках правила 1 алгоритм определения типов используют различные приоритеты преобразований. Далее показаны преобразования в порядке убывания приоритета.

    1. Преобразование между типами с одним базовым типом, имеющими разную длину.

    2. Преобразование между версиями одного типа данных с фиксированной длиной и переменной длиной (например, из char в varchar).

    3. Преобразование между NULL и int.

    4. Все прочие преобразования.

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

Наконец, в запросе SELECT NULL + @p для @p выбирается тип int, поскольку для него требуется преобразование третьего класса.

Разрешения

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

Примеры

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

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.

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_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)