Способы вызова хранимой процедуры

Выполнение хранимой процедуры

Применимо к: SQL Server (все поддерживаемые версии) База данных SQL Azure Управляемый экземпляр SQL Azure Azure Synapse Analytics Параллельное хранилище данных

В этом разделе описывается, как выполнить хранимую процедуру SQL Server при помощи среды SQL Server Management Studio или Transact-SQL.

Существует два способа выполнения хранимой процедуры. Первым и наиболее распространенным подходом является вызов процедуры приложением или пользователем. Второй подход — настройка автоматического выполнения процедуры при запуске экземпляра SQL Server . Если процедура вызывается приложением или пользователем, то в вызове явно указывается ключевое слово Transact-SQL EXECUTE или EXEC. Процедуру также можно вызывать и выполнять без ключевого слова, если она является первой инструкцией в пакете Transact-SQL .

В этом разделе

Перед началом работы

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

Перед началом

Ограничения

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

Чтобы показать точные имена системных процедур, запросите представления каталога sys.system_objects и sys.system_parameters .

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

Рекомендации

Выполнение системных хранимых процедур

Имена системных процедур начинаются с префикса sp_ . Поскольку они логически отображаются во всех базах данных, определяемых и пользователем, и системой, то они могут выполняться из любой базы данных без полного указания имени процедуры. Однако рекомендуется уточнять имена всех системных процедур указанием схемы sys во избежание конфликтов имен. В следующем примере демонстрируется рекомендуемый метод вызова системной процедуры.

Выполнение пользовательских хранимых процедур

При выполнении определяемой пользователем процедуры рекомендуется дополнительно указывать имя схемы. Это позволяет немного увеличить производительность, поскольку компоненту Компонент Database Engine не нужно выполнять поиск в нескольких схемах. Также исключается выполнение неправильной процедуры в случае, если в нескольких схемах базы данных имеются процедуры с одним именем.

В следующем примере демонстрируется рекомендуемый метод выполнения определяемой пользователем процедуры. Обратите внимание, что процедура принимает один входной параметр. Сведения об указании входных и выходных параметров см. в статье Указание параметров.

Если не указано уточненное имя определяемой пользователем процедуры, компонент Компонент Database Engine производит поиск процедуры в следующем порядке.

схема sys текущей базы данных;

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

Схема dbo в текущей базе данных.

Автоматическое выполнение хранимых процедур

Процедуры, помеченные для автоматического выполнения, выполняются каждый раз, когда запускается SQL Server и в процессе запуска восстанавливается база данных master . Настройка процедур для автоматического выполнения удобна для операций обслуживания базы данных и для постоянного выполнения процедур в фоновом процессе. Кроме того, автоматический запуск процедур может применяться для выполнения системных или служебных задач в базе данных tempdb, таких как создание глобальной временной таблицы. Это обеспечивает наличие такой временной таблицы при повторном создании базы данных tempdb во время запуска SQL Server .

Автоматически выполняемая процедура работает с теми же разрешениями, что и члены предопределенной роли сервера sysadmin . Любое сообщение об ошибке, сформированное такой процедурой, записывается в журнал ошибок SQL Server .

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

Не возвращайте никаких результирующих наборов из автоматически запускаемой процедуры. Эта хранимая процедура выполняется SQL Server , а не приложением или пользователем, и поэтому результирующие наборы нигде не обрабатываются.

Установка, очистка и контроль автоматического выполнения

Помечать процедуру для автоматического выполнения может только системный администратор (sa). Кроме того, процедура должна находиться в базе данных master , принадлежать пользователю sa и не иметь входных или выходных параметров.

Используйте процедуру sp_procoption чтобы:

обозначить существующую процедуру как автоматически запускаемую;

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

безопасность

Permissions

Дополнительные сведения см. в разделе «Разрешения» статьи EXECUTE (Transact-SQL).

Использование среды SQL Server Management Studio

Выполнение хранимой процедуры

В обозревателе объектов подключитесь к экземпляру компонента Компонент SQL Server Database Engine, разверните его, а затем разверните узел Базы данных.

Разверните нужную базу данных, разверните узлы Программирование и Хранимые процедуры.

Щелкните правой кнопкой мыши определяемую пользователем хранимую процедуру и выберите команду Выполнить хранимую процедуру.

В диалоговом окне Выполнение процедуры укажите значение для каждого параметра и необходимость передачи значения NULL.

Параметр
Указывает имя параметра.

Тип данных
Указывает тип данных параметра.

Выходной параметр
Указывает, является ли этот параметр выходным.

Передать значение NULL
Передать значение NULL в качестве значения параметра.

Значение
Введите значение параметра, передаваемое ему при вызове процедуры.

Чтобы выполнить хранимую процедуру, нажмите кнопку ОК.

Использование Transact-SQL

Выполнение хранимой процедуры

Установите соединение с компонентом Компонент Database Engine.

На панели «Стандартная» нажмите Создать запрос.

Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить. В этом примере показано, как выполнить хранимую процедуру, которая принимает один параметр. В примере выполняется хранимая процедура uspGetEmployeeManagers со значением 6 , указанным в параметре @EmployeeID .

Установка и отмена автоматического запуска процедуры

Установите соединение с компонентом Компонент Database Engine.

На панели «Стандартная» нажмите Создать запрос.

Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить. В этом примере показано, как использовать процедуру sp_procoption , чтобы задать автоматическое выполнение процедуры.

Отмена автоматического выполнения процедуры

Установите соединение с компонентом Компонент Database Engine.

На панели «Стандартная» нажмите Создать запрос.

Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить. В этом примере показано, как использовать процедуру sp_procoption , чтобы отменить автоматическое выполнение процедуры.

Источник

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

Область применения: SQL Server Analysis Services Azure Analysis Services Power BI Premium

Хранимые процедуры можно вызывать на сервере или из клиентского приложения. В любом случае хранимые процедуры всегда выполняются на сервере либо в контексте сервера, либо в контексте базы данных. Для выполнения хранимой процедуры не нужны специальные разрешения. После того как хранимая процедура добавлена сборкой к контексту сервера или базы данных, любой пользователь может выполнить эту хранимую процедуру, если только роль для этого пользователя разрешает действия, выполняемые этой хранимой процедурой.

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

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

Вызов хранимых процедур в запросах многомерных выражений

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

Возврат набора

В следующих примерах реализуется хранимая процедура, имеющая имя MySproc, которая возвращает набор. В первом примере MySproc возвращает набор непосредственно в выражении SELECT. В следующих двух примерах MySproc возвращает набор в качестве аргумента для функций Crossjoin и DrilldownLevel.

Возврат элемента

В следующем примере показана функция MySproc, возвращающая элемент:

Возврат результата математической операции

Вызов хранимых процедур с использованием инструкции Call

Хранимые процедуры можно вызывать вне контекста запроса многомерных выражений с помощью инструкции MDX Call .

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

Единственным поддерживаемым типом, возвращаемым хранимой процедурой в операторе Call , является набор строк. Упорядочивание для набора строк определяется XML для аналитики. Если хранимая процедура в инструкции Call возвращает любой другой тип, она игнорируется и не возвращается в XML вызывающему приложению. Дополнительные сведения о наборах строк XML для аналитики см. в разделе «Наборы строк схемы XML для аналитики».

Если хранимая процедура возвращает набор строк платформы .NET, то службы Службы Analysis Services преобразуют результат на сервере в набор строк XML для аналитики. XML для аналитики набор строк всегда возвращается хранимой процедурой в функции Call . Если набор данных содержит элементы, которые нельзя выразить в наборе строк XML для аналитики, то происходит сбой.

Процедуры, возвращающие значения void (например, подпрограммы языка Visual Basic), также могут использоваться с ключевым словом CALL. Например, если необходимо использовать функцию MyVoidFunction() в инструкции многомерного выражения, то нужно применить следующий синтаксис:

Источник

Вызов хранимой процедуры

Применимо к: SQL Server (все поддерживаемые версии) База данных SQL Azure Управляемый экземпляр SQL Azure Azure Synapse Analytics Параллельное хранилище данных

SQL ServerДрайвер ODBC для собственного клиента поддерживает как escape-последовательность ODBC CALL, так и Transact-SQL инструкцию EXECUTE для выполнения хранимых процедур; в качестве предпочтительного метода используется escape-последовательность вызова ODBC. Использование синтаксиса ODBC позволяет приложению получать коды возврата хранимых процедур, а драйвер ODBC для собственного клиента SQL Server оптимизирован в целях использования протокола, первоначально разработанного для отправки вызовов удаленных процедур (RPC) между компьютерами, на которых выполняется SQL Server. Этот протокол RPC повышает производительность, устраняя большую часть обработки параметров и синтаксической проверки инструкций на сервере.

При вызове SQL Server хранимых процедур с использованием именованных параметров с помощью ODBC (Дополнительные сведения см. в разделе Привязка параметров по имени (именованные параметры)). имена параметров должны начинаться с @ символа «». Это ограничение, характерное для SQL Server. В драйвере ODBC для собственного клиента SQL Server это ограничение контролируется строже, чем в компонентах доступа к данным MDAC.

Управляющая последовательность ODBC CALL для вызова процедуры такова:

где procedure_name указывает имя процедуры, а параметр — параметр процедуры. Именованные параметры поддерживаются только в инструкциях, использующих escape-последовательности ODBC CALL.

Процедура может иметь параметры или не иметь их. Она также может возвращать значение (на что указывает необязательный маркер параметра «?=» в начале синтаксической конструкции). Если параметр является входным или входным-выходным, то может представлять собой литерал или маркер параметра. Если параметр является выходным, то должен быть маркером параметра, поскольку выходной параметр неизвестен. Маркеры параметров должны быть привязаны к SQLBindParameter перед выполнением инструкции вызова процедуры.

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

Если процедура не имеет ни одного параметра, ее вызов завершается ошибкой. Если процедура вызывается без скобок, драйвер не передает какие-либо значения параметров. Пример:

В вызовах процедур можно задавать литералы для входных или входных-выходных параметров. Например, процедура InsertOrder имеет пять входных параметров. В следующем вызове процедуры InsertOrder пропущен первый параметр, указан литерал для второго параметра и используется маркер параметра для третьего, четвертого и пятого параметра. (Параметры нумеруются последовательно, начиная с 1.)

Обратите внимание, что даже если параметр пропущен, запятая, отделяющая его от других параметров, должна присутствовать. Если пропущен входной или входной-выходной параметр, процедура использует значение по умолчанию. Другие способы задать значение по умолчанию для входного или входного-выходного параметра таковы: присвоить значение буфера длины и индикатора, привязанное к параметру процедуры SQL_DEFAULT_PARAM, или использовать ключевое слово DEFAULT.

Если входной-выходной параметр пропущен или в качестве параметра выступает литерал, то драйвер отбрасывает выходное значение. Аналогичным образом, если пропущен маркер параметра для значения, возвращаемого процедурой, драйвер отбрасывает возвращаемое значение. Наконец, если в приложении задан параметр возвращаемого значения для процедуры, которая не возвращает значение, драйвер задает значение буфера длины и индикатора, привязанное к параметру процедуры SQL_NULL_DATA.

Разделители в инструкциях CALL

Драйвер ODBC для собственного клиента SQL Server по умолчанию поддерживает также параметр совместимости для управляющей последовательности ODBC < CALL >. Он принимает инструкции CALL только с одним набором двойных кавычек, ограничивающих все имя хранимой процедуры:

По умолчанию драйвер ODBC для собственного клиента SQL Server принимает также инструкции CALL, которые соответствуют правилам ISO, и заключает каждый идентификатор в двойные кавычки:

Но при выполнении с настройками по умолчанию драйвер ODBC для собственного клиента SQL Server не поддерживает использование ни одной из форм представления идентификаторов, заключенных в кавычки, применительно к идентификаторам, которые содержат символы, не указанные как допустимые в стандарте ISO. Например, драйвер не может получить доступ к хранимой процедуре с именем «My. proc» с помощью инструкции Call с заключенными в кавычки идентификаторами:

Эта инструкция интерпретируется драйвером следующим образом:

Сервер вызывает ошибку, связанную с тем, что связанный сервер с именем myDB не существует.

При использовании идентификаторов, заключенных в квадратные скобки, эта инструкция интерпретируется правильно:

Источник