С сайта DEVELS.RU (уже не работает)

Хранимая процедура представляет собой запрос, хранящийся в БД SQL Server. Она повышает скорость и эффективность БД и вызывается командой

Exec имя_хранимой_процедуры

Базовая хранимая процедура.

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

пример: допустим у нас есть таблица «Sotrydnik» БД “Ludi”. Необходимо извлечь информацию о сотрудниках, получающих зарплату более 30 000 рублей.

1. Открыть SQL Server Management Studio. В окне Object Explorer развернутьпапкуБД «Ludi” — Programmability – Stored Procedures.
2. Щелкаем правой кнопкой мыши на папке Stored Procedures и выбираем команду New Stored Procedures. В открывшемся коде удаляем ненужную информацию и в итоге имеем следующий шаблон:

CREATE PROCEDURE
AS
BEGIN

SELECT ,
END
GO

3. Далее вместо Procedure_Name указываем имя процедуры (как она у нас будет называться). В поле SELECTпишем необходимый текст запроса. В результате в нашем примере хранимая процедура будет выглядеть следующим образом:

CREATE PROCEDURE dlastat1
AS
BEGIN
SELECT * from Sotrydnik Where Oklad>=30000
END
GO

4. Выполняем процедуру, нажав на кнопку на панели инструментов. В результате в нижней части экрана мы увидим строчку «Command(s) completedsuccessfully».
5. Далее сохраняем процедуру, щелкнув по кнопке Save
6. Чтобы протестировать новую процедуру, необходимо создать запрос, нажав на кнопку New Query , следующего содержания
Use
Exec

В нашем примере:
Use Ludi
exec dlastat1

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

Итак, теперь для получения информации пользователи будут пересылать по сети всего лишь одну строчку кода exec dlastat1.

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

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

пример: допустим у нас есть таблица «Sotrydnik» БД “Ludi”. Необходимо извлечь информацию о сотрудниках, получающих зарплату более некоторого входного параметра рублей. Модифицируем предыдущую процедуру.

1. Открыть SQL Server Management Studio. В окне Object Explorer развернутьпапкуБД «Ludi” — Programmability – Stored Procedures.
2. Шелкнуть правой кнопкой на хранимой процедуре dlastat1 и выбрать команду Modify.
3. Модифицируем ранее созданную процедуру таким образом:

CREATE PROCEDURE dlastat1
@OKL int
AS
BEGIN
SELECT * from Sotrydnik
Where Oklad>=@okl
END
GO

где @OKL входной параметр типа int

4. Выполняем процедуру, нажав на кнопку на панели инструментов. В результате в нижней части экрана мы увидим строчку «Command(s) completedsuccessfully».
5.Далее сохраняем процедуру, щелкнув по кнопке Save

Итак, у нас имеется модифицированная хранимая процедура dlastat1, принимающая параметр пользователя.

Протестируем процедуру.

6. Чтобы протестировать новую процедуру, необходимо создать запрос, нажав на кнопку New Query , следующего содержания
Use
Exec ‘входной параметр’

В нашем примере:
Use Ludi
exec dlastat1 ‘40000’

Выполняем запрос, щелкнув по кнопке , в нижней части окна увидим список сотрудников, оклад которых, более 40000.

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

пример: допустим у нас есть таблица «Sotrydnik» БД “Ludi”. Необходимо извлечь информацию о сотрудниках, получающих зарплату более некоторого входного параметра рублей, если входной параметр не введен, то значение по умолчанию будет 25000 рублей.

1. Открыть SQL Server Management Studio. В окне Object Explorer развернутьпапкуБД «Ludi” — Programmability – Stored Procedures.
2. Шелкнуть правой кнопкой на хранимой процедуре dlastat1 и выбрать команду Modify.
3. Модифицируем ранее созданную процедуру таким образом:

CREATE PROCEDURE dlastat1
@OKL [int]=’25000’
AS
BEGIN
SELECT * from Sotrydnik
Where Oklad>=@okl
END
GO

где @OKL входной параметр типа int

4. Выполняем процедуру, нажав на кнопку на панели инструментов. В результате в нижней части экрана мы увидим строчку «Command(s) completedsuccessfully».
5.Далее сохраняем процедуру, щелкнув по кнопке Save

Итак, у нас имеется модифицированная хранимая процедура dlastat1, принимающая параметр пользователя, и использующая значение по умолчанию, при отсутствии входного параметра.

Протестируем процедуру.

6. Чтобы протестировать новую процедуру, необходимо создать запрос, нажав на кнопку New Query , следующего содержания
Use
Exec

В нашем примере:
Use Ludi
exec dlastat1

Выполняем запрос, щелкнув по кнопке , в нижней части окна увидим список сотрудников, оклад которых, более 25000 рублей.

Использование выходных параметров.

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

пример: вводим два числа и находим их сумму.

1. Открыть SQL Server Management Studio. В окне Object Explorer развернутьпапкуБД «Ludi” — Programmability – Stored Procedures.
2. Щелкаемправойкнопкоймышинапапке Stored Procedures ивыбираемкоманду New Stored Procedures.
3. Набираем код хранимой процедуры, который будем иметь вид:

CREATE PROCEDURE calc
@first [int],
@sec [int],
@ret [int] output
AS
BEGIN
SET @ret=@first+@sec
END
GO

4. Выполняем процедуру, нажав на кнопку на панели инструментов. В результате в нижней части экрана мы увидим строчку «Command(s) completedsuccessfully».
5.Далее сохраняем процедуру, щелкнув по кнопке Save

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

1. создать запрос, нажав на кнопку New Query , следующего содержания

Use Ludi
Declare @answer int
exec calc 3,6,
@answer Output
Select ‘Сумма двух чисел равна:’,@answer

2. Выполняем запрос, щелкнув по кнопке , в нижней части окна увидим:
Сумма двух чисел равна: 9

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

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

CREATE PROCEDURE dlastat1
@OKL [int]=’25000’
WITH RECOMPiLE, EXECUTE AS CALLER
AS
BEGIN
SELECT * from Sotrydnik
Where Oklad>=@okl
END
GO

Предложение WITHRECOMPLE указывает SQLServer создавать новый план выполнения при каждом запуске хранимой процедуры без сохранения ее в кеше.
Если же нужно менять план очень редко, то следует использовать метод EXECUTE … WITHRECOMPLE. Эта инструкция указывает SQLServerсоздавать план лишь один раз. При использовании метода EXECUTE … WITHRECOMPLEкод создания хранимой процедуры НЕ изменится, изменится вызов процедуры:

EXEC dlastat1 WITH RECOMPLE

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