Хранимые процедуры— вызов в собственном клиенте SQL Server

Статья
01/10/2024

Применимо: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure azure Synapse Analytics Analytics Platform System (PDW)

Внимание

Собственный клиент SQL Server (часто сокращенный SNAC) был удален из SQL Server 2022 (16.x) и SQL Server Management Studio 19 (SSMS). Поставщик OLE DB собственного клиента SQL Server (SQLNCLI или SQLNCLI11) и устаревший поставщик Microsoft OLE DB для SQL Server (SQLOLEDB) не рекомендуется для новой разработки. Перейдите на новый драйвер Microsoft OLE DB (MSOLEDBSQL) для SQL Server .

Хранимая процедура может иметь ноль и более параметров. Также она может возвращать значение. При использовании поставщика OLE DB собственного клиента SQL Server параметры хранимой процедуры можно передать следующим образом:

Заданные в коде значения данных.
Указание параметров с помощью маркера параметра (?), привязка программной переменной к маркеру параметра и последующее помещение значений данных в программную переменную.

Примечание.

При вызове хранимых процедур SQL Server с использованием именованных параметров в OLE DB имена параметров должны начинаться со знака "@". Это определенное ограничение SQL Server. Поставщик OLE DB собственного клиента SQL Server применяет это ограничение более строго, чем MDAC.

Для поддержки параметров объект команды предоставляет интерфейс ICommandWithParameters. Чтобы использовать параметры, потребитель сначала описывает параметры поставщика путем вызова метода ICommandWithParameters::SetParameterInfo (или при необходимости готовит инструкцию, вызывающую метод GetParameterInfo). Затем потребитель создает метод доступа, определяющий структуру буфера и помещающий значения параметров в этот буфер. После этого он передает дескриптор метода доступа и указатель на буфер функции Execute. При последующих вызовах Execute потребитель помещает новые значения параметров в буфер и вызывает функцию Execute с дескриптором метода доступа и указателем буфера.

Команда, вызывающая временную хранимую процедуру, использующую параметры, сначала должна вызвать метод ICommandWithParameters::SetParameterInfo для определения сведений о параметрах, чтобы команду можно было успешно подготовить. Это происходит по той причине, что внутреннее имя временной хранимой процедуры отличается от внешнего имени, используемого клиентом, а SQLOLEDB не может запрашивать системные таблицы, чтобы определить сведения о параметрах временной хранимой процедуры.

Далее приведены шаги процесса привязки параметров.

Внесите сведения о параметре (имя параметра, специфическое для поставщика имя типа данных параметра или стандартное имя типа данных и т. д.) в массив структур DBPARAMBINDINFO. Каждая структура в массиве описывает один параметр. Затем этот массив передается методу SetParameterInfo.
Вызовите метод ICommandWithParameters::SetParameterInfo, чтобы описать параметры для поставщика. Метод SetParameterInfo определяет собственный тип данных каждого параметра. Метод SetParameterInfo использует следующие аргументы.
- Количество параметров, для которых задаются сведения о типе.
- Массив порядковых номеров параметров, для которых задаются сведения о типе.
- Массив структур DBPARAMBINDINFO.
Создайте метод доступа к параметру с помощью команды IAccessor::CreateAccessor. Метод доступа указывает структуру буфера и помещает в буфер значения параметров. Команда CreateAccessor создает метод доступа на основе набора привязок. Эти привязки описываются потребителем с помощью массива структур DBBINDING. Каждая привязка связывает отдельный параметр с буфером потребителя и содержит следующие сведения.
- Порядковый номер параметра, к которому применяется привязка.
- Что привязывается (значение данных, длина и состояние).
- Смещение в буфере для каждой из этих частей.
- Длина и тип значения данных в том виде, в котором оно представлено в буфере потребителя.
Метод доступа определяется его дескриптором типа HACCESSOR. Это дескриптор возвращается методом CreateAccessor. Когда потребитель завершает использование метода доступа, он должен вызвать метод ReleaseAccessor, чтобы освободить занимаемую память.

Когда потребитель вызывает метод, например ICommand::Execute, он передает дескриптор методу доступа, а указатель непосредственно буферу. Поставщик использует этот метод доступа, чтобы определить способ передачи данных из буфера.
Заполните структуру DBPARAMS. Переменные потребителя, из которых берутся значения входных параметров и в которые записываются значения выходных параметров, передаются во время выполнения методу ICommand::Execute в структуре DBPARAMS. Структура DBPARAMS включает три следующих элемента.
- Указатель на буфер, из которого поставщик получает данные входных параметров и в который возвращает данные выходных параметров в соответствии с привязками, указанными дескриптором метода доступа.
- Количество наборов параметров в буфере.
- Дескриптор метода доступа, созданный на шаге 3 .
Выполните команду с помощью метода ICommand::Execute.

Методы вызова хранимых процедур

При выполнении хранимой процедуры в SQL Server поставщик OLE DB собственного клиента SQL Server поддерживает следующее:

Escape-последовательность ODBC CALL.
Escape-последовательность удаленного вызова процедур (RPC).
Инструкция Transact-SQL EXECUTE.

Escape-последовательность ODBC CALL

Если известны сведения о параметре, вызовите метод ICommandWithParameters::SetParameterInfo, чтобы описать параметры для поставщика. В противном случае, если для вызова хранимой процедуры используется синтаксис ODBC CALL, поставщик вызывает вспомогательную функцию для поиска сведений о параметрах хранимой процедуры.

Если сведения о параметрах (метаданные параметров) точно неизвестны, рекомендуется применять синтаксис ODBC CALL.

Общий синтаксис для вызова процедуры с помощью escape-последовательности ODBC CALL выглядит следующим образом.

{[?=]callprocedure_name[([parameter][,[parameter]]...)]}

Например:

{call SalesByCategory('Produce', '1995')}

Escape-последовательность RPC

Escape-последовательность RPC похожа на синтаксис ODBC CALL для вызова хранимой процедуры. Если нужно вызывать процедуру несколько раз, escape-последовательность RPC обеспечивает наиболее оптимальную производительность из всех трех методов вызова хранимой процедуры.

Если escape-последовательность RPC используется для выполнения хранимой процедуры, поставщик не вызывает вспомогательные функции для определения сведений о параметре, как в случае применения синтаксиса ODBC CALL. Синтаксис RPC проще синтаксиса ODBC CALL, поэтому команда анализируется быстрее, что увеличивает производительность. В этом случае необходимо указать сведения о параметрах путем выполнения метода ICommandWithParameters::SetParameterInfo.

Escape-последовательность RPC требует наличия возвращаемого значения. Если хранимая процедура не возвращает значение, сервер по умолчанию возвращает 0. Кроме того, нельзя открыть курсор SQL Server в хранимой процедуре. Хранимая процедура подготавливается неявно, и вызов метода ICommandPrepare::Prepare завершится ошибкой. Запрашивать метаданные столбцов нельзя, поскольку невозможно подготовить вызов RPC. Методы IColumnsInfo::GetColumnInfo и IColumnsRowset::GetColumnsRowset будут возвращать значение DB_E_NOTPREPARED.

Если известны все метаданные параметров, для выполнения хранимых процедур рекомендуется использовать escape-последовательность RPC.

Далее приведен пример escape-последовательности RPC для вызова хранимой процедуры.

{rpc SalesByCategory}

Пример приложения, которое демонстрирует применение escape-последовательности RPC, см. в статье Выполнение хранимой процедуры с использованием RPC и обработка выходных данных.

Инструкция Transact-SQL EXECUTE

Escape-последовательность ODBC CALL и escape-последовательность RPC — это предпочтительные способы вызова хранимых процедур в отличие от инструкции EXECUTE. Поставщик OLE DB собственного клиента SQL Server использует механизм RPC SQL Server для оптимизации обработки команд. Этот протокол RPC повышает производительность, устраняя большую часть обработки параметров и синтаксической проверки инструкций на сервере.

Это пример инструкции Transact-SQL EXECUTE :

EXECUTE SalesByCategory 'Produce', '1995'

См. также

Хранимые процедуры

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