sp_describe_undeclared_parameters (Transact-SQL)

  • Статья

Применимо к:SQL Server База данных 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'parameters' data type ] [, ...n]

Примечание.

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

Аргументы

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

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

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

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

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

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

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

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

Имя столбца Тип данных Description
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 всегда возвращает состояние возврата нулевого значения.

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

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

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

    • Целые типы данных (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

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

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

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

    Например:

    SELECT * FROM t WHERE Col_Int = Col_Int + @p

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

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

    SELECT * FROM t WHERE Col_Int = Col_smallint + @p

    В этом случае int и smallint создают одно преобразование. Для любого другого типа данных требуется несколько преобразований. Так как int имеет приоритет над smallint, int используется для @p. Дополнительные сведения о приоритете типов данных см. в разделе "Приоритет типа данных" (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), так как преобразование (a) лучше всего. Для запроса SELECT * FROM t WHERE [Col_char(30)] > @pvarchar(8000) по-прежнему выбран, так как он вызывает преобразование типа (b), а другой выбор (например, varchar(4000)) приведет к преобразованию типа (d).

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

Разрешения

Требуется разрешение на выполнение аргумента @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'

См. также