Create ms sql procedure: CREATE PROCEDURE (Transact-SQL) — SQL Server

Содержание

Как создать хранимую процедуру в SQL Server Management Studio (SSMS)

Введение

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

  1. Что такое хранимая процедура в SQL?
  2. Почему мы используем SET NOCOUNT ON в хранимой процедуре?
  3. Сколько существует типов хранимых процедур?
  4. Как писать комментарии в SQL Server?
  5. Каковы соглашения об именовании хранимых процедур?
  6. Как создать хранимую процедуру для выбора данных из таблицы базы данных с помощью запроса SELECT SQL?
  7. Как выполнять хранимые процедуры в SQL Server?
  8. Какие параметры хранимых процедур?
  9. Как создать параметры в хранимой процедуре запроса SELECT, которая возвращает записи в соответствии с переданным параметром?
  10. Как создать хранимую процедуру на основе запроса INSERT?
  11. Как создать хранимую процедуру на основе запроса UPDATE?
  12. Как создать хранимую процедуру для удаления записей с помощью запроса DELETE?

Что такое хранимая процедура?

Хранимая процедура SQL (SP) представляет собой набор операторов SQL и логики команд SQL, которые компилируются и хранятся в базе данных. Хранимые процедуры в SQL позволяют нам создавать SQL-запросы, которые будут храниться и выполняться на сервере. Хранимые процедуры также можно кэшировать и использовать повторно. Основная цель хранимых процедур — скрыть прямые SQL-запросы от кода и повысить производительность операций с базой данных, таких как выбор, обновление и удаление данных. Вот подробная статья о Хранимые процедуры в SQL .

Вы можете создавать и выполнять хранимые процедуры с помощью обозревателя объектов в SQL Server или с помощью SQL Server Management Studio (SSMS). Если вы новичок в SSMS, попробуйте SQL Server Management Studio .

Почему мы используем SET NOCOUNT ON в хранимой процедуре?

Хотя мы установили SET NOCOUNT ON, это означает, что нет сообщений, показывающих количество затронутых строк.

NOCOUNT означает, что значение ON не учитывается.

Теперь вы узнаете, что произошло, когда SET NOCOUNT OFF.

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

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

  1. Пользовательские хранимые процедуры
  2. Системные хранимые процедуры

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

Пользовательские хранимые процедуры создаются разработчиками или администраторами баз данных. Эти SP содержат еще одну инструкцию SQL для выбора, обновления или удаления записей из таблиц базы данных. Определяемые пользователем хранимые процедуры могут принимать входные параметры и возвращать выходные параметры. Определяемая пользователем хранимая процедура представляет собой смесь команд DDL (язык определения данных) и DML (язык манипулирования данными).

Пользовательские SP далее подразделяются на два типа:

  • Хранимые процедуры T-SQL — T-SQL (Transact SQL) SP получают и возвращают параметры. Эти SP обрабатывают запросы на вставку, обновление и удаление с параметрами или без них и возвращают данные строк в качестве выходных данных. Это один из наиболее распространенных способов написания SP в SQL Server.
     
  • Хранимые процедуры CLR-   CLR (Common Language Runtime) SP написаны на языке программирования на основе CLR, таком как C# или VB.NET, и выполняются .NET Framework.

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

Системные хранимые процедуры создаются и выполняются SQL Server для административной деятельности сервера. Разработчики обычно не вмешиваются в системные SP.

Вход в базу данных SQL Server

Давайте войдем в нашу базу данных SQL Server, чтобы получить следующее:

  • Как создать хранимую процедуру на основе SELECT QUERY, которая возвращает все записи?
  • Как создать хранимую процедуру SELECT QUERY на основе PARAMETER, которая возвращает записи на основе параметров?
  • Как создать хранимую процедуру на основе запроса INSERT?
  • Как создать хранимую процедуру на основе запроса UPDATE?
  • Как создать хранимую процедуру на основе запроса DELETE?

Войдите в SQL SERVER, используя имя сервера, имя пользователя и пароль.

 

Переключитесь на свою базу данных. Имя моей базы данных MBKTest.

 

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

 

Пустой шаблон, созданный SQL Server для SP, выглядит следующим образом. SQL-команда CREATE PROCEDURE используется для создания процедуры, за которой следует имя SP и ее параметры. Область BEGIN и END используется для определения запроса операции. Здесь вы будете писать запросы выбора, обновления, вставки или удаления.

 -- ============================================ ==
-- Шаблон, сгенерированный из Template Explorer с использованием:
-- Создать процедуру (Новое меню).SQL
--
-- Используйте параметр "Указать значения для параметров шаблона".
-- команда (Ctrl-Shift-M) для заполнения параметра
-- значения ниже.
--
-- Этот блок комментариев не будет включен в
-- определение процедуры.
-- ===============================================
УСТАНОВИТЕ ANSI_NULLS ВКЛ.
ИДТИ
УСТАНОВИТЬ QUOTED_IDENTIFIER ON
ИДТИ
-- ============================================
-- Автор:      <Автор,Имя>
-- Дата создания: <Дата создания,>
-- Описание: <Описание,>
-- ============================================
CREATE PROCEDURE 
-- Добавьте сюда параметры для хранимой процедуры
<@Param1, sysname, @p1>  = ,
<@Param2, sysname, @p2>  = 
В КАЧЕСТВЕ
НАЧИНАТЬ
-- Добавлено SET NOCOUNT ON, чтобы предотвратить появление дополнительных наборов результатов
-- вмешательство в операторы SELECT. 
УСТАНОВИТЬ NOCOUNT ON;

-- Вставьте операторы для процедуры здесь
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
КОНЕЦ
ВПЕРЕД

Как писать комментарии в SQL SERVER?

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

 -- (два дефиса/тире) для одной строки комментария.
начните с /* ……. заканчиваться на  */    для многострочных комментариев.

Каково соглашение об именовании хранимых процедур?

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

В соответствии с соглашением об именовании определяемых пользователем хранимых процедур я предлагаю добавить один из следующих префиксов к вашим именам SP.

  • сп
  • стп
  • стп_
  • удстп
  • удстп_

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

Создать таблицу базы данных

Прежде чем мы сможем создавать и выполнять какие-либо SP, нам нужна таблица базы данных. Я создаю таблицу базы данных с именем «tblMembers», используя следующий запрос SQL, и выполняю его на сервере. Как видите, в моей таблице 4 столбца, где первый столбец является столбцом идентификаторов. После создания таблицы откройте ее в SSMS и добавьте некоторые данные, введя данные в таблицу вручную.

 ИСПОЛЬЗОВАТЬ [MBKTest]
ИДТИ

/****** Объект:  Table [dbo].[tblMembers]    Script Date: 18 ноября 2017, суббота 18:47:55 ******/
УСТАНОВИТЕ ANSI_NULLS ВКЛ.
ИДТИ

УСТАНОВИТЬ QUOTED_IDENTIFIER ON
ИДТИ

УСТАНОВИТЕ ANSI_PADDING ON
ИДТИ

CREATE TABLE [dbo].[tblMembers](
[MemberID] [int] IDENTITY(1,1) NOT NULL,
[MemberName] [varchar](50) NULL,
[MemberCity] [varchar](25) NULL,
[MemberPhone] [varchar](15) NULL
)

ИДТИ

ВЫКЛЮЧИТЬ ANSI_PADDING
GO  

Как создать хранимую процедуру SELECT?

Нажмите на свою базу данных, разверните элемент «Программируемость» и щелкните правой кнопкой мыши «Хранимые процедуры» или нажмите CTRL + N, чтобы открыть новое окно запроса. В области запроса между BEGIN и END введите оператор SELECT, чтобы выбрать записи из таблицы. См. оператор Select в приведенном ниже коде.

 УСТАНОВИТЬ ANSI_NULLS ВКЛ.
ИДТИ
УСТАНОВИТЬ QUOTED_IDENTIFIER ON
ИДТИ
-- ============================================
-- Автор:      Маной Калла
-- Дата создания: 18 ноября 2017 г.
-- Описание: Вернуть всех участников
-- ============================================
--Имя процедуры сохранения --> stpGetAllMembers
СОЗДАТЬ ПРОЦЕДУРУ stpGetAllMembers
В КАЧЕСТВЕ
НАЧИНАТЬ
-- Добавлено SET NOCOUNT ON, чтобы предотвратить появление дополнительных наборов результатов
-- вмешательство в операторы SELECT.
УСТАНОВИТЬ NOCOUNT ON;

-- Выберите операторы для процедуры здесь
Выберите * из tblMembers
КОНЕЦ
ВПЕРЕД

Теперь нажмите F5 или нажмите кнопку «Выполнить», чтобы выполнить SP.

 

 

Вы должны увидеть сообщение «Команды выполнены успешно».

Теперь перейдите в «Программируемость» -> «Хранимые процедуры», щелкните правой кнопкой мыши и выберите «Обновить».

На следующем изображении видно, что создается новый SP с именем stpGetAllMembers.

 

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

В приведенном ниже пользовательском интерфейсе щелкните правой кнопкой мыши имя SP и выберите «Выполнить хранимую процедуру…», чтобы выполнить SP. Отсюда вы также можете изменить существующий SP.

 

Кроме того, вы можете выполнить SP из окна запроса.

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

  • Синтаксис — EXEC <имя хранимой процедуры>
  • Пример — EXEC stpGetAllMembers

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

ВЫВОД

 

Какие параметры хранимых процедур?

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

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

Как создать SELECT запрос SP с параметрами?

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

Вот обновленный SP с параметром @CityName.

 УСТАНОВИТЬ ANSI_NULLS ВКЛ.
ИДТИ
УСТАНОВИТЬ QUOTED_IDENTIFIER ON
ИДТИ
-- ============================================
-- Автор:      Маной Калла
-- Дата создания: 20 ноября 2017 г.
-- Описание: возвращает записи о конкретных городах.
-- ============================================
СОЗДАТЬ ПРОЦЕДУРУ stpGetMembersByCityName
-- Добавьте сюда параметры для хранимой процедуры
@CityName nvarchar(30)

В КАЧЕСТВЕ
НАЧИНАТЬ
-- Добавлено SET NOCOUNT ON, чтобы предотвратить появление дополнительных наборов результатов
-- вмешательство в операторы SELECT. 
УСТАНОВИТЬ NOCOUNT ON;

Выберите * От tblMembers
где MemberCity например '%'+@CityName+'%'

КОНЕЦ
ВПЕРЕД

Выполнить.

Чтобы запустить этот SP, введите следующую команду в инструменте запросов SQL:

EXEC GetMemberByCityName   @CityName = ‘mal’

ИЛИ из пользовательского интерфейса запустите SP и введите следующие данные.

 

Код для выполнения выглядит следующим образом:

 USE [MBKTest]
ИДТИ

DECLARE @return_value целое число

EXEC    @return_value = [dbo].[GetMemberByCityName]
@CityName = N'mal'

SELECT  'Возвращаемое значение' = @return_value

ВПЕРЕД

ВЫВОД

 

Как создать хранимую процедуру на основе запроса INSERT?

Мы можем использовать SQL-запрос INSERT INTO для вставки данных в таблицу. Следующая инструкция SQL создает INSERT SP с тремя параметрами.

 УСТАНОВИТЬ ANSI_NULLS ВКЛ.
ИДТИ
УСТАНОВИТЬ QUOTED_IDENTIFIER ON
ИДТИ
-- ============================================
-- Автор:      Маной Калла
-- Дата создания: 20 ноября 2047 г. 
-- Описание: Чтобы создать нового участника
-- ============================================
СОЗДАТЬ ПРОЦЕДУРУ stpInsertMember
@MemberName varchar(50),
@MemberCity varchar(25),
@MemberPhone varchar(15)

В КАЧЕСТВЕ
НАЧИНАТЬ
-- Добавлено SET NOCOUNT ON, чтобы предотвратить появление дополнительных наборов результатов
-- вмешательство в операторы SELECT.
УСТАНОВИТЬ NOCOUNT ON;

Вставить в tblMembers (MemberName,MemberCity,MemberPhone)
Значения (@MemberName, @MemberCity, @MemberPhone)

КОНЕЦ
ВПЕРЕД

Щелкните правой кнопкой мыши хранимую процедуру в обозревателе объектов и выберите «Обновить».

 

Передайте значение параметра в диалоговом окне «Выполнить». Примерно так:

 

Следующий код можно использовать для выполнения этого SP в SSMS.

 ИСПОЛЬЗОВАНИЕ [MBKTest]
ИДТИ

DECLARE @return_value целое число

EXEC    @return_value = [dbo].[stpInsertMember]
@MemberName = Н'Махеш Чанд',
@MemberCity = N'NewYork',
@MemberPhone = N'9999945121'
SELECT  'Возвращаемое значение' = @return_value
GO  

OUTPUT

В окне запроса вы можете проверить, добавлена ​​ли в таблицу новая запись для имени участника «Махеш Чанд».

 

Вы также можете запустить тот же SP в коде.

 EXEC STPINSERTMEMEMN @MemberName = 'Suhana & Ashish Kalla', @membercity = 'mumbai', @memberphone = n'
92774xxx '

Вывод

Вы можете проверить «Suhana & Ashish halla kalla hout worpt

. .

 

Как создать хранимую процедуру на основе запроса UPDATE?

Давайте создадим новый SP, который будет обновлять запись таблицы на основе столбца идентификатора участника. Идентификатор передается как входной параметр. Вот новый SP, который использует команду UPDATE..SET..WHERE.

 УСТАНОВИТЬ ANSI_NULLS ВКЛ.
ИДТИ
УСТАНОВИТЬ QUOTED_IDENTIFIER ON
ИДТИ
-- ============================================
-- Автор:      Маной Калла
-- Дата создания: 20 ноября 2017 г.
-- Описание: Обновление сведений об участнике по идентификатору.
-- ============================================
СОЗДАТЬ ПРОЦЕДУРУ stpUpdateMemberByID
@MemberID целое число,
@MemberName varchar(50),
@MemberCity varchar(25),
@MemberPhone varchar(15)

В КАЧЕСТВЕ
НАЧИНАТЬ
-- Добавлено SET NOCOUNT ON, чтобы предотвратить появление дополнительных наборов результатов
-- вмешательство в операторы SELECT. 
УСТАНОВИТЬ NOCOUNT ON;

ОБНОВЛЕНИЕ tblMembers
Установите MemberName = @MemberName,
MemberCity = @MemberCity,
MemberPhone = @MemberPhone
Где MemberID = @MemberID
КОНЕЦ
ВПЕРЕД

Щелкните правой кнопкой мыши хранимую процедуру в обозревателе объектов и выберите «Обновить». Вы увидите, что SP создан.

Теперь щелкните правой кнопкой мыши имя SP и выберите «Выполнить хранимую процедуру…». Укажите входные значения и выполните.

 

Мы можем использовать следующую команду в SSMS.

 ИСПОЛЬЗОВАНИЕ [MBKTest]
ИДТИ

DECLARE @return_value целое число

EXEC    @return_value = [dbo].[stpUpdateMemberByID]
@MemberID = 20,
@MemberName = Н'Нирупама Калла',
@MemberCity = Н'Мумбаи',
@MemberPhone = N'2541хххх

SELECT  'Возвращаемое значение' = @return_value

GO

EXEC stpUpdateMemberByID 17,’Gopal Madhavrai’,’Bikaner’,’564xxx’

В результатах должны отображаться обновленные значения.

 

Как создать хранимую процедуру на основе запроса DELETE?

Давайте создадим SP, который будет удалять записи. Новый SP использует команду DELETE и удаляет все записи, соответствующие предоставленному идентификатору участника.

 УСТАНОВИТЬ ANSI_NULLS ВКЛ.
ИДТИ
УСТАНОВИТЬ QUOTED_IDENTIFIER ON
ИДТИ
-- ============================================
-- Автор:      Маной Калла
-- Дата создания: 21 ноября 2017 г.
-- Описание: Удалить участника по идентификатору участника
-- ============================================
СОЗДАТЬ ПРОЦЕДУРУ stpDeleteMemberByMemberID
@MemberID целое число
В КАЧЕСТВЕ
НАЧИНАТЬ
-- Добавлено SET NOCOUNT ON, чтобы предотвратить появление дополнительных наборов результатов
-- вмешательство в операторы SELECT.
УСТАНОВИТЬ NOCOUNT ON;

Удалить из tblMembers
где MemberId = @MemberID

КОНЕЦ
ВПЕРЕД

Выполнить.

Щелкните правой кнопкой мыши хранимые процедуры в обозревателе объектов и выберите «Обновить».

ЗАПУСК хранимой процедуры через пользовательский интерфейс

Теперь снова щелкните правой кнопкой мыши хранимую процедуру и выберите Выполнить хранимую процедуру…

Как вы можете видеть на изображении, я передал значение параметра @MemberID = 4.

 

ЗАПУСК УДАЛИТЬ хранимую процедуру ВРУЧНУЮ (КОДИРОВАНИЕ)

EXEC stpDeleteMemberByMemberID  2

ВЫВОД

На изображении видно, что MemberID = 4 записи успешно удалены.

 

Заключение

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

Хранимые процедуры SQL Server: создание, изменение, переименование, выполнение

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

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

Хранимые процедуры бывают двух типов:

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

Системные процедуры: Системные процедуры входят в состав SQL Server и физически хранятся во внутренней скрытой базе данных ресурсов и логически появляются в схеме sys всех баз данных.
Системные хранимые процедуры начинаются с префикса sp_ .

Создать хранимую процедуру

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

 СОЗДАТЬ [ИЛИ ИЗМЕНИТЬ] {PROC | PROCEDURE} [имя_схемы.] имя_процедуры([@параметр тип_данных [ OUT | OUTPUT | [ТОЛЬКО ДЛЯ ЧТЕНИЯ]]
[С <опция_процедуры>]
[ДЛЯ РЕПЛИКАЦИИ]
    В КАЧЕСТВЕ
    НАЧИНАТЬ
        sql_statements
    КОНЕЦ

Хранимая процедура может содержать один или несколько операторов выбора, вставки, обновления или удаления.
Ниже приведен пример простой хранимой процедуры, которая возвращает записи из таблицы Employee 9.0355 с помощью запроса SELECT.

 СОЗДАТЬ ПРОЦЕДУРУ uspGetEmployeeList
В КАЧЕСТВЕ
НАЧИНАТЬ
   ВЫБЕРИТЕ EmpID
,Имя
,Фамилия
   ОТ dbo. Employee
КОНЕЦ

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

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

Следующая хранимая процедура вставляет значения в таблицу Employee .

 СОЗДАТЬ ПРОЦЕДУРУ dbo.uspInsertEmployee
(
 @FirstName nvarchar(50)
, @LastName nvarchar(50)
,@Электронная почта nvarchar(50)
,@PhoneNo nvarchar(20)
,@Зарплата деньги
)
В КАЧЕСТВЕ
НАЧИНАТЬ
ВСТАВИТЬ В dbo.Employee
           (Имя
           ,Фамилия
           ,Эл. адрес
           ,Телефонный номер
           ,Оплата труда)
    ЗНАЧЕНИЯ
(
@Имя
,@Фамилия
,@Эл. адрес
,@Телефонный номер
,@Оплата труда
)
 КОНЕЦ

Описанную выше хранимую процедуру можно использовать для вставки значений в таблицу Employee вместо оператора INSERT.
Значения передаются в качестве параметров хранимой процедуре. Символ @ используется в качестве префикса для переменных параметров.

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

 EXEC dbo.uspInsertEmployeeDetails
@FirstName = 'Свати'
,@LastName = 'Кария'
,@Email = '[электронная почта защищена]'
, @PhoneNo = '6657890980'
,@Зарплата = 300000

Укажите каждый параметр, разделенный командой, при выполнении хранимой процедуры.

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

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

Все хранимые процедуры перечислены в папке «Программируемость» > «Хранимые процедуры» в базе данных.

Изменить хранимую процедуру

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

 ПРОЦЕДУРА ИЗМЕНЕНИЯ dbo.uspGetEmployees
В КАЧЕСТВЕ
НАЧИНАТЬ
   ВЫБЕРИТЕ EmpID
,Имя
,Фамилия
     ,Оплата труда
   ОТ dbo.Employee
КОНЕЦ

Переименование хранимой процедуры

Используйте системную хранимую процедуру sp_rename для переименования существующей хранимой процедуры.
Следующие переименовывает uspGetEmployeeList от до uspGetEmployees .

 sp_rename 'uspGetEmployeeList', 'uspGetEmployees'

Удалить хранимую процедуру

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

 ПРОЦЕДУРА УДАЛЕНИЯ dbo.uspGetEmployees;

Обработка исключений в хранимых процедурах

В SQL Server блок TRY..CATCH используется для корректной обработки исключений. Группа операторов T-SQL может быть заключена в блок TRY. Если в блоке TRY возникает ошибка, управление затем передается блоку CATCH, который будет иметь другой набор операторов SQL для обработки ошибки.

В блоке CATCH для получения информации об ошибке можно использовать такие системные функции, как ERROR_NUMBER() , ERROR_STATE() , ERROR_SEVERITY() .

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

 СОЗДАТЬ ПРОЦЕДУРУ uspUpdateEmpSalary
(
     @empId целое
     ,@зарплата с плавающей запятой
)
В КАЧЕСТВЕ
НАЧАТЬ ПОПРОБУЙТЕ
    ОБНОВЛЕНИЕ dbo.Employee
    УСТАНОВИТЬ Зарплата = @зарплата
    ГДЕ СотрудникID = @empId
КОНЕЦ ПОПЫТКИ
НАЧАТЬ ЛОВИТЬ
    ВЫБРАТЬ
     ERROR_NUMBER() КАК ErrorNumber
     ,ERROR_SEVERITY() AS ErrorSeverity
     ,ERROR_STATE() КАК ErrorState
     ,ERROR_MESSAGE() AS ErrorMessage;
КОНЦЕВОЙ ЗАХВАТ

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

  • Хранимые процедуры можно использовать повторно. Несколько пользователей в нескольких приложениях могут использовать одну и ту же хранимую процедуру (SP).