Создание и управление индексами. Создание и управление встроенными процедурами. Оптимизация производительности запросов. Управление транзакциями и блокировками, страница 15

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

  Если вы создаёте системные процедуры определённые пользователем, вы должны войти под учётной записью администратора и использовать базу данных master;

  Создавайте процедуры так, чтобы они выполняли по одной задаче;

  Создавайте и тестируйте процедуры на сервере, а потом тестируйте их на клиенте;

  Не используйте префикс sp_ когда создаёте локальные процедуры, чтобы в дальнейшем не было разногласий.

  Все процедуры должны использовать одинаковую строку подключения.

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

  Используйте sp_executesql вместо использования оператора EXECUTE для динамического выполнения строки в процедуре.

  Никогда не удаляйте напрямую содержимое таблицы syscomments.

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

Вы можете выполнять процедуры с помощью операторов EXECUTE или INSERT.

Синтаксис команды EXECUTE:

[ [ EXEC [ UTE ] ]

{

[ @return_status = ]

{ procedure_name [ ;number ] | @procedure_name_var

}

[ [ @parameter = ] { value | @variable [ OUTPUT ] | [ DEFAULT ] ]

[ ,...n ]

[ WITH RECOMPILE ]

Оператор INSERT может заполнять локальную таблицу результирующим набором, который возвращается из локальной или удалённой процедуры. Сервер SQL заполняет таблицу данными, которые возвращаются оператором SELECT в процедуре. Таблица должна существовать и типы данных должны совпадать.

Следующий пример создаёт процедуру EmployeeCustomer, которая вставляет работников в таблицу Customers:

USE Northwind

GO

CREATE PROC dbo.EmployeeCustomer

AS

SELECT *

FROM dbo.Employees

GO

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

INSERT INTO Customers

EXEC EmployeeCustomer

Изменение и удаление процедур

Для изменения процедуры используйте оператор ALTER PROCEDURE. Сервер заменяет существующее описание процедуры тем, что указано в ALTER PROCEDURE.

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

Рассматривайте следующие факты, когда используете оператор ALTER PROCEDURE:

  Если вы хотите изменить процедуру, которая была создана с какими-нибудь опциями, например WITH ENCRYPTION, вы должны включить эти опции в опции ALTER PROCEDURE, для сохранения функциональности, которую предоставляет опция.

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

  Вы должны обладать необходимыми правами, для выполнения операции изменения процедуры.

Синтаксис:

ALTER PROC [ EDURE ] procedure_name [ ; number ]

[ { @parameter data_type }

[ VARYING ] [ = default ] [ OUTPUT ]

] [ ,...n ]

[ WITH

{ RECOMPILE | ENCRYPTION

| RECOMPILE , ENCRYPTION

}

]

[ FOR REPLICATION ]

AS

sql_statement [ ...n ]

Следующий пример изменяет процедуру OverdueOrders:

USE Northwind

GO

ALTER PROC dbo.EmployeeCustomer

AS

SELECT *

FROM dbo.Employees

ORDER BY LastName

GO

Для удаления процедуры используйте DROP PROCEDURE. Перед удалением воспользуйтесь процедурой sp_depends для определения связанных объектов.

Синтаксис:

DROP PROCEDURE { procedure } [ ,...n ]

9.3 Лабораторные

Написание и выполнение процедуры

Следующий пример создаёт процедуру, которая выбирает 5 популярных продуктов из таблицы Products:

USE ClassNorthwind

GO

IF EXISTS (SELECT * FROM dbo.sysobjects

WHERE id = object_id(N'[dbo].[FiveMostExpensiveProducts]') and

OBJECTPROPERTY(id, N'IsProcedure') = 1)

DROP PROCEDURE [dbo].[FiveMostExpensiveProducts]

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

CREATE PROCEDURE [FiveMostExpensiveProducts]