Базовое руководство по хранимым процедурам SQL Server

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

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

Следующий оператор SELECT возвращает список продуктов из таблицы products в образце базы данных BikeStores:

SELECT 
 product_name, 
 list_price
FROM 
 production.products
ORDER BY 
 product_name;

Чтобы создать хранимую процедуру, которая оборачивает этот запрос, используйте оператор CREATE PROCEDURE следующим образом:

CREATE PROCEDURE uspProductList
AS
BEGIN
    SELECT 
        product_name, 
        list_price
    FROM 
        production.products
    ORDER BY 
        product_name;
END;

В этом синтаксисе:

• uspProductList — это имя хранимой процедуры.
• Ключевое слово AS разделяет заголовок и тело хранимой процедуры.
• Если хранимая процедура имеет один оператор, ключевые слова BEGIN и END, окружающие оператор, необязательны. Однако рекомендуется включать их, чтобы сделать код понятным.

Обратите внимание, что в дополнение к ключевым словам CREATE PROCEDURE вы можете использовать ключевые слова CREATE PROC, чтобы сделать оператор короче.

Чтобы скомпилировать эту хранимую процедуру, выполните ее как обычный оператор SQL в SQL Server Management Studio, как показано на следующем рисунке:

Если все правильно, то вы увидите следующее сообщение:

Commands completed successfully.

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

Хранимая процедура находится в обозревателе объектов в разделе Программирование > Хранимая процедура, как показано на следующем рисунке:

Иногда вам нужно нажать кнопку «Обновить», чтобы вручную обновить объекты базы данных в обозревателе объектов.

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

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

EXECUTE sp_name;

Или

EXEC sp_name;

где sp_name — имя хранимой процедуры, которую вы хотите выполнить.

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

EXEC uspProductList;

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

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

Чтобы изменить существующую хранимую процедуру, используйте оператор ALTER PROCEDURE.

Сначала откройте хранимую процедуру, чтобы просмотреть ее содержимое, щелкнув правой кнопкой мыши имя хранимой процедуры и выбрав пункт меню «Изменить»:

Во-вторых, измените тело хранимой процедуры, отсортировав продукты по прейскурантным ценам, а не по наименованиям продуктов:

 ALTER PROCEDURE uspProductList
    AS
    BEGIN
        SELECT 
            product_name, 
            list_price
        FROM 
            production.products
        ORDER BY 
            list_price 
    END;

В-третьих, нажмите кнопку «Выполнить», SQL Server изменит хранимую процедуру и вернет следующий вывод:

Commands completed successfully.

Теперь, если вы снова выполните хранимую процедуру, вы увидите, что изменения вступили в силу:

EXEC uspProductList;

Ниже показан частичный вывод:

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

Чтобы удалить хранимую процедуру, используйте оператор DROP PROCEDURE или DROP PROC:

DROP PROCEDURE sp_name;

или

DROP PROC sp_name;    

где sp_name — имя хранимой процедуры, которую вы хотите удалить.

Например, чтобы удалить хранимую процедуру uspProductList, выполните следующую инструкцию:

DROP PROCEDURE uspProductList;

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