Sql server как найти хранимую процедуру

Данный материал является переводом оригинальной статьи “MSSQLTips : Rick Dobson : Create, Alter, Drop and Execute SQL Server Stored Procedures”.

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

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

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

Сохранение кода в хранимой процедуре, а не в файле сценария дает несколько преимуществ. Вот несколько примеров:

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

Хранимые процедуры вводят некий уровень абстракции, которого нет при сохранении кода в файле сценария. Следовательно, если у вас есть простое решение, используемое одним пользователем, которому требуется доступ к базовым источникам данных для запроса (или набора запросов), то файл сценария может быть даже лучше, поскольку он упрощает решение.

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

После создания или изменения хранимой процедуры, содержащей один или несколько операторов SELECT, вы можете вызвать хранимую процедуру с помощью оператора EXEC. Следовательно, вы можете думать о хранимой процедуре как о контейнере, который облегчает повторное использование в нем кода T-SQL.

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

Многие администраторы баз данных знакомы с созданием таблицы с помощью оператора CREATE TABLE. Точно так же администраторы-разработчики могут создать хранимую процедуру с помощью оператора CREATE PROC или CREATE PROCEDURE. Так же, как оператор CREATE TABLE добавляет таблицу в базу данных, оператор CREATE PROC добавляет хранимую процедуру в базу данных. Итак, вам нужно начать с новой или существующей базы данных, когда вы хотите создать хранимую процедуру, поскольку хранимая процедура фактически хранится в базе данных.

Если у вас есть соответствующие разрешения, вы можете использовать оператор CREATE DATABASE, чтобы создать новую базу данных для хранения таблиц и других типов объектов, таких как хранимые процедуры.

Следующий сценарий создает базу данных с именем CodeModuleTypes. Его первый оператор определяет главную базу данных как базу данных по умолчанию. Его второй оператор создает базу данных. В зависимости от ваших потребностей могут быть гораздо более сложные версии оператора CREATE DATABASE. Если не указано иное, простой оператор создания БД, подобный приведенному ниже, будет использовать настройки по умолчанию из БД model, которая является одной из стандартных баз данных, устанавливаемых вместе с SQL Server.

use master;
GO
create database CodeModuleTypes;

После того, как у вас появилась база данных, такая как CodeModuleTypes, вы можете вызвать оператор CREATE PROC в этой базе данных.

Следующий сценарий демонстрирует синтаксис, который можно использовать для создания вашей первой хранимой процедуры. Хранимая процедура в приведенном ниже коде отображает набор результатов со всеми столбцами для каждой строки из таблицы Employee в схеме HumanResources демонстрационной базы данных AdventureWorks2014. Ранее мы описали, как загрузить копию этой демонстрационной БД.

Вы можете думать о схеме, как о способе логической группировки объектов базы данных, таких как таблицы и хранимые процедуры. Эти логические группировки позволяют избежать конфликтов имен между объектами с одинаковыми именами в разных схемах. Любая база данных может иметь несколько схем. В нашем примере все хранимые процедуры обозначены, как принадлежащие схеме dbo базы данных CodeModuleTypes.

Приведенный ниже оператор CREATE PROC состоит из трех частей.

• Оператор CREATE PROC называет хранимую процедуру (и её схему, если вы явно указываете её).
• Ключевое слово as действует как маркер, обозначающий, что код определения хранимой процедуры вот-вот начнется.
• Код T-SQL, определяющий работу хранимой процедуры. В этом примере определяющим кодом является оператор SELECT для таблицы Employee в схеме HumanResources базы данных AdventureWorks2014.

use CodeModuleTypes;
go   create proc dbo.uspMyFirstStoredProcedure
as
select * 
from AdventureWorks2014.HumanResources.Employee;

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

exec dbo.uspMyFirstStoredProcedure

Вот отрывок из вывода, созданного предыдущим скриптом.

• На панели результатов показаны первые одиннадцать столбцов из первых семнадцати строк с данными о 290 сотрудниках компании AdventureWorks.
• Если вы хотите обработать строки, отображаемые оператором SELECT в хранимой процедуре, вам нужно будет сохранить строки набора результатов в какой-либо другой таблице или объекте SQL Server. Затем обработайте результаты в этом объекте.

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

Предыдущий сценарий создания хранимой процедуры завершится ошибкой, если хранимая процедура uspMyFirstStoredProcedure в схеме dbo уже существует. Одним из способов решения этой проблемы является удаление предыдущей версии хранимой процедуры, а затем повторный запуск сценария для создания новой версии хранимой процедуры. Вы можете удалить предыдущую версию хранимой процедуры с помощью оператора DROP PROC или DROP PROCEDURE.

drop proc dbo.uspMyFirstStoredProcedure

Вместо того, чтобы позволить оператору CREATE PROC завершиться с ошибкой при наличии предыдущей версии хранимой процедуры, обычно проверяют, существует ли уже хранимая процедура, и удаляют ее, чтобы избежать ошибки. Следующий фрагмент можно использовать для удаления предыдущей версии хранимой процедуры uspMyFirstStoredProcedure, если она уже существует. В зависимости от ваших требований и кода, определяющего хранимую процедуру, может быть полезно переименовать существующую в настоящее время хранимую процедуру вместо ее удаления.

-- conditionally drop a stored proc
if object_id('dbo.uspMyFirstStoredProcedure') is not null 
     drop proc dbo.uspMyFirstStoredProcedure
go

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

Следующий блок кода демонстрирует оператор ALTER PROC. Этот оператор отличается от оператора CREATE PROC тем, что он может работать только с существующей хранимой процедурой.

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

Первые два оператора в следующем блоке кода – это операторы CREATE PROC и EXEC, которые могут создавать новую копию хранимой процедуры и запускать uspMyFirstStoredProcedure. Поскольку весь код представлен в виде одного блока, требуются три ключевых слова GO, которые не потребовались бы, если бы весь блок кода был сегментирован на четыре отдельных блока кода:

• Начальный оператор CREATE PROC должен сопровождаться ключевым словом GO, чтобы оператор CREATE PROC завершился перед первым оператором EXEC.
• Затем начальный оператор EXEC должен сопровождаться ключевым словом GO, чтобы оператор ALTER PROC был первым оператором в его пакете.
• Наконец, за оператором ALTER PROC должно следовать ключевое слово GO, чтобы оператор ALTER PROC завершился до последнего оператора EXEC.

Оператор ALTER PROC состоит из трех частей:

• Имя объекта после ALTER PROC должно совпадать с именем существующей хранимой процедуры, которую вы хотите изменить.
• Ключевое слово as действует как разделитель, отделяющий объявления ALTER PROC от нового кода T-SQL, определяющего измененную версию хранимой процедуры.
• Новый код внутри оператора ALTER PROC соединяет таблицу Person из схемы Person с таблицей Employee из схемы HumanResources в базе данных AdventureWorks2014.

-- create a new stored proc
create proc dbo.uspMyFirstStoredProcedure
as
select * 
from AdventureWorks2014.HumanResources.Employee
go   -- run stored proc
exec dbo.uspMyFirstStoredProcedure
go   -- alter stored proc
alter proc 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
go   -- run altered stored proc
exec dbo.uspMyFirstStoredProcedure

Вот результат выполнения оператора EXEC в предыдущем сценарии:

• Столбцы BusinessEntityID и JobTitle взяты из таблицы Employee.
• Столбцы FirstName и LastName взяты из таблицы Person.

Помимо очевидной разницы между созданием новой хранимой процедуры и изменением существующей хранимой процедуры, оператор ALTER PROC отличается от оператора CREATE PROC другими важными особенностями. Например, оператор ALTER PROC сохраняет все параметры безопасности, связанные с существующей сохраненной процедурой, в то время, как оператор CREATE PROC не сохраняет эти параметры. Таким образом, оператор ALTER PROC подойдет лучше, чем оператор CREATE PROC, если все, что нужно сделать, это изменить код в существующей хранимой процедуре.

Начиная с SQL Server 2016 SP1, Microsoft представила новую инструкцию CREATE или ALTER для модулей кода, таких как хранимые процедуры, представления и определяемые пользователем функции. Вы можете получить представление о функциональности этого нового оператора из предыдущей статьи MSSQLTips.com.

Входные параметры хранимой процедуры SQL Server

Входной параметр позволяет разработчику изменять способ работы хранимой процедуры во время выполнения. Обычно входной параметр в предложении where оператора SELECT используется для управления строками, отображаемыми при запуске хранимой процедуры. Можно заглянуть на страницу руководства MSSQLTips.com для демонстрации того, как использовать входные параметры в предложении where.

Вот простой пример, основанный на изменении uspMyFirstStoredProcedure, который демонстрирует использование входного параметра.

Входной параметр с именем @jobtitle назван непосредственно перед ключевым словом as.

На входной параметр имеется ссылка в предложении where оператора SELECT.

Оператор EXEC, который следует за оператором ALTER PROC, присваивает значение входному параметру @jobtitle во время выполнения. Значением параметра является строка nvarchar (“Production Supervisor”).

-- alter a stored proc-- this alteration has one select statement with a where clause
-- and a criterion set by an input parameter
-- and an input parameter
alter proc 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 + '%'
go   -- run altered stored proc with 
-- @jobtitle parameter value is passed without naming the parameter
exec uspMyFirstStoredProcedure N'Production Supervisor'

Вот вкладка “Results“, на которой показаны выходные данные оператора SELECT внутри uspMyFirstStoredProcedure. На вкладке показан 21 сотрудник, чьи должности начинаются с “Production Supervisor”. Вы можете изменить содержимое вкладки “Results”, используя другое строковое значение в операторе EXEC.

Параметры вывода хранимых процедур SQL Server

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

Если вы просто хотите передать одно значение, такое как сумма или количество, из хранимой процедуры, вы можете сделать это с помощью выходного параметра. Следующий оператор ALTER PROC иллюстрирует один из способов реализации такого рода задач.

В нашем примере оператор ALTER PROC снова изменяет uspMyFirstStoredProcedure.

Входной параметр @jobtitle из предыдущей версии хранимой процедуры сохраняется.

Кроме того, перед ключевым словом as добавляется спецификация выходного параметра:

• Имя выходного параметра – @jobtitlecount.
• Тип данных для параметра – int, потому что он предназначен для хранения значения счетчика, но вы также можете использовать bigint в качестве типа данных, если это необходимо.
• Ключевое слово out завершает спецификацию параметра, чтобы указать, что этот параметр возвращает значение после запуска хранимой процедуры.

Оператор SELECT состоит из вложенного внутреннего оператора выбора внутри внешнего операторе выбора:

• Оператор внутреннего выбора возвращает строку для каждого сотрудника, JobTitle которого начинается со значения входного параметра.
• Внешний оператор select подсчитывает количество строк, возвращаемых внутренним оператором select, и присваивает счет выходному параметру @jobtitlecount.

-- alter a stored proc
-- this alteration computes an aggregate function value
-- based, in part, on an input parameter (@jobtitle)
-- and saves the computed value in an output parameter (@jobtitlecount)
alter proc dbo.uspMyFirstStoredProcedure
@jobtitle nvarchar(50), @jobtitlecount int out
as   select @jobtitlecount = count(*)
from AdventureWorks2014.HumanResources.Employee
inner join AdventureWorks2014.Person.Person on Employee.BusinessEntityID = Person.BusinessEntityID
where Employee.JobTitle like @jobtitle + '%'

После выполнения оператора ALTER PROC можно вызвать недавно измененную версию uspMyFirstStoredProcedure и отобразить значение выходного параметра. Следующий сценарий показывает, как этого добиться.

Перед вызовом инструкции EXEC для запуска uspMyFirstStoredProcedure объявите локальную переменную @jobtitlecount для получения значения выходного параметра из хранимой процедуры.

• Значение параметра вывода появляется в операторе EXEC с завершающим ключевым словом вывода OUTPUT. Это ключевое слово указывает, что значение параметра передается из хранимой процедуры в инструкцию EXEC.
• Оператор присваивания (=) передает значение выходного параметра в локальную переменную @jobtitlecount.

Оператор SELECT после оператора EXEC отображает значение локальной переменной @jobtitlecount, которая получила значение выходного параметра.

-- run an altered stored proc with -- @jobtitle input parameter value and
-- save the returned output parameter in a local variable   declare @jobtitlecount int   exec uspMyFirstStoredProcedure N'Production Supervisor',@jobtitlecount = @jobtitlecount OUTPUT   select @jobtitlecount [Job Title Count]

Значения кода возврата хранимой процедуры SQL Server

Хранимые процедуры могут иметь значения кода возврата, которые всегда имеют тип данных int.

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

• Входной параметр имеет имя @jobtitle.
• Критерий предложения where в операторе SELECT: Employee.JobTitle, например ‘%’ + @jobtitle + ‘%’.
• Когда инструкция SELECT с предложением where возвращает хотя бы одну строку, возвращаемое значение устанавливается равным единице. В противном случае возвращаемое значение устанавливается равным нулю.
• Условие EXISTS определяет, будет ли возвращена хотя бы одна строка из оператора SELECT.
• Предложение return возвращает значение кода возврата и завершает хранимую процедуру.

-- alter a stored proc
-- this alteration verifies if a search string value
-- is in a set of column values
-- @jobtitle input parameter contains the search string value
-- JobTitle is the column of values searched
alter proc dbo.uspMyFirstStoredProcedure
@jobtitle nvarchar(50)
as   -- does at least one JobTitle contain @jobtitle?
if exists(
   select top 1 Employee.JobTitle
   from AdventureWorks2014.HumanResources.Employee
   inner join AdventureWorks2014.Person.Person on Employee.BusinessEntityID = Person.BusinessEntityID
   where Employee.JobTitle like '%' + @jobtitle + '%'
)
begin
   return(1)
end
else
begin
   return(0)
end

Следующий сценарий демонстрирует синтаксис для сбора значения кода возврата из хранимой процедуры и показывает некоторый образец кода для обработки значения кода возврата. Скрипт вызывает uspMyFirstStoredProcedure для двух разных значений @jobtitle – sals или sales. Ни одно значение столбца JobTitle не содержит sals, но хотя бы одно значение JobTitle содержит sales.

Сначала в коде объявляются две локальные переменные с именами @jobtitle и @exists.

Локальная переменная @jobtitle используется в инструкции EXEC для передачи строки поиска в uspMyFirstStoredProcedure.

Локальная переменная @exists используется для сбора значения кода возврата из uspMyFirstStoredProcedure. Оператор присваивания внутри оператора EXEC заполняет локальную переменную @exists значением кода возврата.

Оператор потока управления if … else после оператора EXEC обрабатывает возвращаемое значение из хранимой процедуры.

• Если @exists равно нулю, инструкция SELECT сообщает, что нет JobTitle со значением строки поиска во входном параметре.
• Если @exists равно единице, оператор SELECT сообщает, что существует по крайней мере одно значение JobTitle с входным параметром.

Ниже, хранимая процедура выполняется дважды. Первоначальное выполнение предназначено для поисковой строки со значением sals. Второе выполнение – для значения sales в строке поиска.

-- run an altered stored proc with 
-- @jobtitle is an input parameter
-- @exists equals 1 for at least 1 JobTitle containing @jobTitle
-- @exists equals 0 for no JobTitle containing @jobtitle
declare @jobtitle nvarchar(50), @exists int   set @jobtitle = 'sals'
exec @exists = uspMyFirstStoredProcedure @jobtitle
if @exists = 0
begin
   select 'No JobTitle values with ' + @jobtitle [search outcome]
end
else
begin
   select 'At least one JobTitle value with ' + @jobtitle [search outcome]
end   set @jobtitle = 'sales'
exec @exists = uspMyFirstStoredProcedure @jobtitle
if @exists = 0
begin
   select 'No JobTitle values with ' + @jobtitle [search outcome]
end
else
begin
   select 'At least one JobTitle value with ' + @jobtitle [search outcome]
end

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

Несколько наборов результатов из хранимой процедуры SQL Server

Следующий сценарий снова демонстрирует, как использовать входной параметр в операторе ALTER PROC. Имя входного параметра перед ключевым словом as – @jobtitle. Эта демонстрация отличается тем, что включает в себя два отдельных оператора SELECT. Первый оператор SELECT возвращает набор результатов, состоящий из всех строк, JobTitle которых начинается со значения входного параметра. Вторая инструкция SELECT возвращает скалярное значение, которое представляет собой количество сотрудников в таблице Employee, JobTitle которых начинается со значения входного параметра.

Оператор EXEC после оператора ALTER PROC вызывает uspMyFirstStoredProcedure. Литеральное строковое значение nvarchar (“Production Supervisor”) после имени хранимой процедуры является значением входного параметра.

-- alter a stored proc-- this alteration has two select statements
-- and an input parameter
alter proc dbo.uspMyFirstStoredProcedure
@jobtitle nvarchar(50)
as   -- 1st select statement returns a set of row values
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 + '%'   -- 2nd select statement returns a scalar value
select count(*) as JobTitleCount
from AdventureWorks2014.HumanResources.Employee
inner join AdventureWorks2014.Person.Person on Employee.BusinessEntityID = Person.BusinessEntityID
where Employee.JobTitle like @jobtitle + '%'

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

-- run altered stored proc
-- @jobtitle parameter value is passed without naming the parameter   
exec dbo.uspMyFirstStoredProcedure N'Production Supervisor'

Вот вкладка “Results”, на которой показаны выходные данные двух операторов SELECT внутри хранимой процедуры с именем uspMyFirstStoredProcedure.

На верхней панели отображается двадцать один сотрудник, чьи должности начинаются с “Production Supervisor”.

На нижней панели отображается скалярное значение с количеством сотрудников, чьи должности начинаются с “Production Supervisor”.

Как вы понимаете, можно изменить содержимое вкладки “Результаты”, используя другое буквальное строковое значение в операторе EXEC.

Пересказ статьи Chad Callihan. Searching a Stored Procedure for Text

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

Как нам проверить эти изменения?

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

SELECT O.name, O.create_date, O.modify_date, m.definition

FROM sys.sql_modules m

INNER JOIN sys.objects o ON m.object_id = o.object_id

WHERE m.definition LIKE '%SEARCH_TEXT%';

GO

Следуя нашему примеру, если мы хотим подтвердить, что база данных имеет процедуру ExampleProc, обновленную до версии 1.2.3, и это указано в хранимой процедуре, мы могли бы увидеть следующее, заменив SEARCH_TEXT в скрипте выше на 1.2.3:

За пределами одной базы данных

Этого достаточно нам для поиска в одной базе данных, но у нас множество баз данных, которые мы хотим просмотреть. Нужно ли нам выполнять этот скрипт на каждой отдельной базе данных поочередно? Вы можете это делать, если хотите, но я не рекомендовал бы вам тратить так много времени. Вместо этого, я бы посоветовал сочетать скрипт выше с процедурой Aaron Bertrand sp_ineachdb.

Возможно, вам знакома системная процедура sp_MSforeachdb, которая доступна в SQL Server по умолчанию. Но вы можете не знать, что хотя она имеется и может быть обнаружена во многих источниках как решение для выполнения запроса на всех базах данных, она фактически не поддерживается. Кроме того, есть обстоятельства, когда базы данных могут быть пропущены, хотя её название – foreachdb – говорит об обратном. Microsoft не собирается тратить время на её исправление, поскольку она официально не поддерживается. Это означает, что вы можете либо самостоятельно проверять выполнение её работы, либо перейти к sp_ineachdb.

Использование sp_ineachdb в нашем примере будет выглядеть примерно так:

EXEC dbo.sp_ineachdb @user_only = 1, @command = N'SELECT DB_NAME() AS ''Database'',O.name, O.create_date, O.modify_date, m.definition

FROM sys.sql_modules m

INNER JOIN sys.objects o ON m.object_id = o.object_id

WHERE m.definition LIKE ''%1.2.3%'';';

Вы можете также использовать @select_dbname=1 в sp_ineachdb, чтобы увидеть каждую базу данных, на которой выполняется ваш скрипт:

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

Хранимые процедуры

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

Последнее обновление: 14.08.2017

Нередко операция с данными представляет набор инструкций, которые необходимо выполнить в определенной последовательности. Например, при добавлении данных покупки товара
необходимо внести данные в таблицу заказов. Однако перед этим надо проверить, а есть ли покупаемый товар в наличии. Возможно, при этом понадобится проверить еще ряд дополнительных условий.
То есть фактически процесс покупки товара охватывает несколько действий, которые должны выполняться в определенной последовательности.
И в этом случае более оптимально будет инкапсулировать все эти действия в один объект – хранимую процедуру (stored procedure).

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

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

И еще один важный аспект – производительность. Хранимые процедуры обычно выполняются быстрее, чем обычные SQL-инструкции. Все потому что код процедур компилируется один раз при первом ее запуске, а затем сохраняется в скомпилированной форме.

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

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

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

CREATE TABLE Products
(
    Id INT IDENTITY PRIMARY KEY,
    ProductName NVARCHAR(30) NOT NULL,
    Manufacturer NVARCHAR(20) NOT NULL,
    ProductCount INT DEFAULT 0,
    Price MONEY NOT NULL
);

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

USE productsdb;
GO
CREATE PROCEDURE ProductSummary AS
SELECT ProductName AS Product, Manufacturer, Price
FROM Products

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

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

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

USE productsdb;
GO
CREATE PROCEDURE ProductSummary AS
BEGIN
	SELECT ProductName AS Product, Manufacturer, Price
	FROM Products
END;

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

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

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

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

EXEC ProductSummary

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

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

DROP PROCEDURE ProductSummary