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 возвращает следующий результирующий набор.

Замечания

Процедура 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)