Хранимые процедуры в Microsoft SQL Server

Хранимые процедуры Создание и выполнение процедур

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

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

Для создания хранимой процедуры применяется команда  CREATE PROCEDURE   или   CREATE PROC .

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

Создадим хранимую процедуру для извлечения данных из этой таблицы:

Команда CREATE PROCEDURE должна вызываться в отдельном пакете, поэтому после команды USE, которая устанавливает текущую базу данных, используется команда  GO  для определения нового пакета.

После имени процедуры должно идти ключевое слово AS.

Для отделения тела процедуры от остальной части скрипта код процедуры помещается в блок BEGIN...END:

Stored Procedures : " width="640"

После добавления процедуры мы ее можем увидеть в узле базы данных в SQL Server Management Studio в подузле  Programmability - Stored Procedures :

Процедурой можно управлять также и через визуальный интерфейс.

Выполнение процедуры

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

Удаление процедуры

Для удаления процедуры применяется команда  DROP PROCEDURE :

Параметры в процедурах

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

Например, пусть в базе данных будет следующая таблица Products:

Определим процедуру, которая будет добавлять данные в эту таблицу:

После названия процедуры идет список входных параметров, которые определяются также как и переменные - название начинается с символа  @ , а после названия идет тип переменной.  И с помощью команды INSERT значения этих параметров будут передаваться в таблицу Products.

Используем эту процедуру:

В примере передаваемые в процедуру значения определяются через переменные. При вызове процедуры ей через запятую передаются значения. При этом значения передаются параметрам процедуры  по позиции . Так как первым определен параметр @name, то ему будет передаваться первое значение - значение переменной @prodName. Второму параметру - @manufacturer передается второе значение - значение переменной @company и так далее. Главное, чтобы между передаваемыми значениями и параметрами процедуры было соответствие по типу данных .

Также можно было бы передать непосредственно значения:

Также значения параметрам процедуры можно передавать по имени:

При передаче параметров по имени параметру процедуры присваивается некоторое значение.

Необязательные параметры

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

  Необязательные параметры лучше помещать в конце списка параметров процедуры.

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

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

При вызове процедуры для выходных параметров передаются переменные с ключевым словом OUTPUT:

Результаат

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

С помощью глобальной переменной  @@IDENTITY  можно получить идентификатор добавленной записи.

При вызове этой процедуры ей также по позиции передаются все входные и выходные параметры:

Возвращение значения

Кроме передачи результата выполнения через выходные параметры хранимая процедура также может возвращать какое-либо значение с помощью оператора   RETURN . Хотя данная возможность во многом нивелирована использованием выходных параметров, через которые можно возвращать результат, тем не менее, если надо возвратить из процедуры одно значение, то вполне можно использовать оператор RETURN.

Например, возвратим среднюю цену на товары:

После оператора RETURN указывается возвращаемое значение. В данном случае это значение переменной @avgPrice.

Вызовем данную процедуру:

Для получения результата процедуры ее значение сохраняется в переменную (в данном случае в переменную @result):

Результат: