Как разработчику или администратору SQL Server важно знать, как упаковать ваши T-SQL скрипты для удобного повторного использования. Один из способов достичь этого – создание и выполнение хранимых процедур. В этой статье мы рассмотрим основы создания, изменения и выполнения хранимых процедур, а также использование входных и выходных параметров и кодов возврата, связанных с хранимыми процедурами.

Обзор хранимых процедур SQL Server

Хранимая процедура – это сохраненный блок кода T-SQL, который может быть выполнен по мере необходимости. Она предлагает несколько преимуществ по сравнению с выполнением T-SQL скриптов напрямую:

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

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

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

CREATE PROCEDURE dbo.uspMyFirstStoredProcedure
AS
SELECT *
FROM AdventureWorks2014.HumanResources.Employee;

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

EXEC dbo.uspMyFirstStoredProcedure;

Изменение существующей хранимой процедуры SQL Server

Если вам нужно изменить существующую хранимую процедуру, вы можете использовать оператор ALTER PROCEDURE. Вот пример:

ALTER PROCEDURE dbo.uspMyFirstStoredProcedure
AS
SELECT
 Employee.BusinessEntityID,
 Person.FirstName,
 Person.LastName,
 Employee.JobTitle
FROM AdventureWorks2014.HumanResources.Employee
INNER JOIN AdventureWorks2014.Person.Person ON Employee.BusinessEntityID = Person.BusinessEntityID;

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

EXEC dbo.uspMyFirstStoredProcedure;

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

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

ALTER PROCEDURE dbo.uspMyFirstStoredProcedure
@jobtitle nvarchar(50)
AS
SELECT
 Employee.BusinessEntityID,
 Person.FirstName,
 Person.LastName,
 Employee.JobTitle
FROM AdventureWorks2014.HumanResources.Employee
INNER JOIN AdventureWorks2014.Person.Person ON Employee.BusinessEntityID = Person.BusinessEntityID
WHERE Employee.JobTitle LIKE @jobtitle + '%';

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

EXEC dbo.uspMyFirstStoredProcedure N'Production Supervisor';

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

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

ALTER PROCEDURE dbo.uspMyFirstStoredProcedure
@jobtitle nvarchar(50),
@jobtitlecount int OUTPUT
AS
SELECT @jobtitlecount = COUNT(*)
FROM AdventureWorks2014.HumanResources.Employee
INNER JOIN AdventureWorks2014.Person.Person ON Employee.BusinessEntityID = Person.BusinessEntityID
WHERE Employee.JobTitle LIKE @jobtitle + '%';

Чтобы получить значение выходного параметра, вы можете объявить локальную переменную и использовать ее в операторе EXEC:

DECLARE @jobtitlecount int;

EXEC dbo.uspMyFirstStoredProcedure N'Production Supervisor', @jobtitlecount = @jobtitlecount OUTPUT;

SELECT @jobtitlecount AS [Job Title Count];

Заключение

Хранимые процедуры – это мощный инструмент в SQL Server, который позволяет упаковывать и повторно использовать код T-SQL. Понимая, как создавать, изменять и выполнять хранимые процедуры, а также как использовать входные и выходные параметры и коды возврата, вы можете улучшить эффективность и поддерживаемость ваших решений в SQL Server.