Sql создание процедуры: MS SQL Server и T-SQL

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

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

  Посмотрели 7930 раз(а)    

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

последнее обновление: 7 августа 2018

Шаг 1.

Если у вас не установлена SQL Server Management Studio нужно скачать и установить SQL Server Management Studio …

Чтобы открыть SQL Server Management Studio, мы нажимаем на иконку на рабочем столе:

 
Появится окно и нажмем кнопку «Connect»:
 

 
Через 20 секунд увидим что SQL Server Management Studio загрузилась:
 

Шаг 2. Создание хранимой процедуры

Если у вас не создана база MyDatabase1 , то создаем базу MyDatabase1…

Раскрываем Databases → MyDatabase1 → Programmability → Stored Procedures
Нажимаем правой клавишей мыши на Stored Procedures и выбираем New → Stored Procedure…

Шаг 3. Напишем код для хранимой процедуры

CREATE PROCEDURE GetBooks

AS

     SELECT * FROM Books

GO

Нажмем на дискету (сохранить) и закройте окошко

Нажмем в меню View → Refresh

Увидим нашу хранимую процедуру

Ваши Отзывы . .. комментарии…

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

Оригинал

Сегодня я бы хотел обсудить с вами тему хранимых процедур в SQL Server 2000-2005. В последнее время их написание занимало львиную долю моего времени на работе и чего уж тут скрывать – по окончанию работы с этим делом осталось достаточно информации, которой с удовольствием поделюсь с тобой %пользовательимя%.
Знания, которыми я собираюсь поделиться, к сожалению,(или к счастью) не добыты мной эмперически, а являются, в большей степени, вольным переводом некоторых статей из буржуйских интернетов.
Итак, как можно понять из названия речь пойдет об оптимизации. Сразу оговорюсь, что все действия, которые я сейчас буду описывать, действительно дают существенный(некоторые больший, некоторые меньший) прирост производительности.
Данная статья не претендует на полное раскрытие темы оптимизации, скорее это собрание практик, которые я применяю в своей работе и могу ручаться за их эффективность. Поехали!

Включай в свои процедуры строку — SET NOCOUNT ON:

С каждым DML выражением, SQL server заботливо возвращает нам сообщение содержащее колличество обработанных записей. Данная информация может быть нам полезна во время отладки кода, но после будет совершенно бесполезной. Прописывая SET NOCOUNT ON, мы отключаем эту функцию. Для хранимых процедур содержащих несколько выражений или\и циклы данное действие может дать значительный прирост производительности, потому как колличество трафика будет значительно снижено.

Transact-SQL

CREATE PROC dbo.ProcName
AS
SET NOCOUNT ON;
—Здесь код процедуры
SELECT column1 FROM dbo.TblTable1
—Перключение SET NOCOUNT в исходное состояние
SET NOCOUNT OFF;
GO

Используй имя схемы с именем объекта:

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

Transact-SQL

SELECT * FROM dbo.MyTable —Вот так делать хорошо
— Вместо
SELECT * FROM MyTable —А так делать плохо
—Вызов процедуры
EXEC dbo.MyProc —Опять же хорошо
—Вместо
EXEC MyProc —Плохо!

Не используй префикс «sp_» в имени своих хранимых процедур:

Если имя нашей процедуры начинается с «sp_», SQL Server в первую очередь будет искать в своей главной базе данных. Дело в том, что данный префикс используется для личных внутренних хранимых процедур сервера. Поэтому его использование может привести к дополнительным расходам и даже неверному результату, если процедура с таким же имененем как у вас будет найдена в его базе.

Используй IF EXISTS (SELECT 1) вместо IF EXISTS (SELECT *):

Чтобы проверить наличие записи в другой таблице, мы используем выражение IF EXISTS. Данное выражение возвращает true если из внутреннего выражения возвращается хоть одно изначение, не важно «1», все колонки или таблица. Возращаемые данные, в принципе никак не используются. Таким образом для сжатия трафика во время передачи данных логичнее использовать «1», как показано ниже:

Transact-SQL

IF EXISTS (SELECT 1 FROM sysobjects
WHERE name = ‘MyTable’ AND type = ‘U’)

Используй TRY-Catch для отлова ошибок:

До 2005 сервера после каждого запроса в процедуре писалось огромное колличество проверок на ошибки. Больше кода всегда потребляет больше ресурсов и больше времени. С 2005 SQL Server’ом появился более правильный и удобный способ решения этой проблемы:

Transact-SQL

BEGIN TRY
—код
END TRY
BEGIN CATCH
—код отлова ошибки
END CATCH

Запись опубликована в рубрике Полезно и интересно с метками optimization. Добавьте в закладки постоянную ссылку.

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

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

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

Базовая структура

Хранимая процедура состоит из имени, параметров и одной или нескольких строк кода, которые выполняются в том порядке, в котором они написаны. Как и многие другие объекты, хранимая процедура содержится внутри схемы вашей базы данных и должна иметь уникальный идентификатор. Мы можем создать хранимую процедуру с помощью CREATE PROCEDURE DDL. Начнем с очень простой процедуры.

СОЗДАТЬ ПРОЦЕДУРУ dbo.GetOne
КАК
  SELECT 1 

Это создаст процедуру с именем GetOne в схеме dbo. Он содержит одну строку кода, и если мы его выполним, он вернет набор результатов из одной строки.

Это не самая интересная и полезная процедура, но она иллюстрирует простую структуру, содержащую имя, без параметров и одну строку кода.

Передовая практика

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

Первое, что я хотел бы упомянуть, это то, что предпочтительно включать ключевые слова BEGIN и END для обозначения вашего кода. Хотя я могу написать такую ​​процедуру, это не очень хорошая практика:

Когда следующий разработчик посмотрит на этот код, вполне возможно, что он решит, что SELECT 2 является посторонним кодом в своем окне редактора, и удалит его. Мы должны прояснить, что является частью нашей процедуры, а что нет. Чтобы было ясно, процедура будет включать весь код в пакете, поэтому нам нужно завершить пакет с помощью GO в конце хранимой процедуры. Этот код должен прояснить это.

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

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

Давайте изменим нашу хранимую процедуру для достижения этих целей. Я буду использовать синтаксис CREATE OR ALTER, который был добавлен в SQL Server 2016. В более старых версиях SQL Server использовался только синтаксис ALTER.

СОЗДАТЬ ИЛИ ИЗМЕНИТЬ ПРОЦЕДУРУ dbo.GetOne
/*
Назначение: Эта процедура возвращает скалярное значение целого числа 1 под именем «Один». 
*/КАК
НАЧИНАТЬ
      ВЫБЕРИТЕ 1 КАК ОДИН
КОНЕЦ
GO 

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

Параметры

Часто мы хотим, чтобы поведение нашей хранимой процедуры немного менялось в зависимости от того, что нужно приложению. Мы контролируем это в хранимых процедурах с параметрами. Параметр — это значение, которое передается в хранимую процедуру как часть вызова. Базовая структура заключается в том, что после имени процедуры мы можем перечислить нужные параметры, разделенные запятыми, с типом данных после каждого. Это очень похоже на то, как мы объявляем переменные.

В качестве примера добавим в нашу хранимую процедуру два параметра.

СОЗДАТЬ ИЛИ ИЗМЕНИТЬ ПРОЦЕДУРУ dbo.GetOne @Param1 INT
                                   , @Param2 VARCHAR(100)
/*
Назначение: Эта процедура возвращает скалярное значение целого числа 1 под именем «Один». 
*/КАК
НАЧИНАТЬ
    ЕСЛИ @Param1 = 10
      УСТАНОВИТЬ @Param1 = 100
    ВЫБЕРИТЕ Один = 1
         , @Param1 КАК Param1
         , @Param2 КАК Param2;
КОНЕЦ;
ИДТИ
  

Как вы можете видеть выше, я назвал два параметра @Param1 и @Param2. Символ @ обозначает, что это переменный параметр, которому присвоено имя. Тип данных может быть любым из допустимых типов данных в SQL Server, включая табличный тип. Я могу использовать эти параметры в своем коде. В этом случае я добавил оператор IF, который сбрасывает значение @Param1, если оно равно 10. Затем я использую оба параметра как часть запроса в своем коде.

Практически нет ограничений на количество параметров. Существует ограничение в 2100, но это вряд ли будет практичным уровнем для использования в каком-либо коде.

Параметры по умолчанию

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

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

СОЗДАТЬ ИЛИ ИЗМЕНИТЬ ПРОЦЕДУРУ dbo.GetOne @Param1 INT = 1
                                   , @Param2 VARCHAR(100) = 'Значение по умолчанию'
   , @Param3 INT = ноль
/*
Назначение: Эта процедура возвращает скалярное значение целого числа 1 под именем «Один».
*/КАК
НАЧИНАТЬ
    ЕСЛИ @Param1 = 10
      УСТАНОВИТЬ @Param1 = 100
    ВЫБЕРИТЕ Один = 1
         , @Param1 КАК Param1
         , @Param2 КАК Param2
         , @Param3 КАК Param3;
КОНЕЦ;
GO 

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

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

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

Выходные параметры

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

СОЗДАТЬ ИЛИ ИЗМЕНИТЬ ПРОЦЕДУРУ dbo.GetOne @Param1 INT = 1 ВЫВОД
                                   , @Param2 VARCHAR(100) = 'Значение по умолчанию'
   , @Param3 INT = null ВЫВОД
/*
Назначение: Эта процедура возвращает скалярное значение целого числа 1 под именем «Один».
*/КАК
НАЧИНАТЬ
    ЕСЛИ @Param1 = 10
      УСТАНОВИТЬ @Param1 = 100
    ВЫБЕРИТЕ Один = 1
         , @Param1 КАК Param1
         , @Param2 КАК Param2
         , @Param3 КАК Param3;
КОНЕЦ;
ИДТИ
 

Теперь в вызывающем коде мы должны объявить переменную, которую мы передаем в качестве параметра. Затем эта переменная передается обратно вызывающему коду с любым значением, которое она содержит в конце процедуры. В этом случае мы передаем два разных параметра, @i и @j, один из которых изменяется хранимой процедурой. Другой остается один.

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

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

Другие параметры

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

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

Параметр ШИФРОВАНИЕ звучит хорошо и действительно запутывает код хранимой процедуры. Если вы не используете этот параметр, код, который вы написали для своей хранимой процедуры, сохраняется в DMV sys.sql_modules. Вы можете увидеть это ниже.

Когда используется параметр ШИФРОВАНИЕ, это не легко читается. Если я зашифрую процедуру, я увижу что-то вроде этого. Для кода есть NULL. Однако код есть, и его много

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

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

Заключение

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

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

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

SQL Server — один из лучших продуктов Microsoft, но не каждый профессионал знает, как его эффективно использовать. Например, некоторым может быть сложно создать хранимую процедуру, но не беспокойтесь, если вы попадете в эту скобку, потому что вы попали в нужное место. Теперь, изучение того, как создавать хранимые процедуры SQL через SQL Server , — это то, что должны знать все профессионалы в этой области.

Создание хранимых процедур SQL через SQL Server проще, чем вы могли подумать, поэтому давайте объясним, что делать. Просто следуйте инструкциям здесь:

1. Щелкните Новый запрос
2. Введите оператор CREATE PROCEDURE
3. Вручную напишите оператор CREATE PROCEDURE
4. Вызов хранимой процедуры

1] Нажмите Новый запрос 901 33

Первым делом Вы должны сделать в этой ситуации, чтобы нажать на кнопку New Query. Это легко сделать, поэтому давайте объясним, как.

• Итак, начнем с открытия инструмента SQL Server.
• Оттуда выберите базу данных, в которой вы хотите создать хранимую процедуру.
• Как только он будет запущен, вы должны как можно скорее нажать кнопку «Новый запрос».
• Вы можете найти эту кнопку прямо на панели инструментов.

2] Введите оператор CREATE PROCEDURE

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

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

 СОЗДАТЬ ПРОЦЕДУРУ LatestTasks @Count int AS
УСТАНОВИТЬ ROWCOUNT @Count
ВЫБЕРИТЕ имя задачи AS LatestTasks, DateCreated
ОТ Задачи
ORDER BY DateCreated DESC 

Следует отметить, что приведенный выше сценарий предназначен для создания хранимой процедуры с именем TastestTasks и принимает параметр с именем Count.

3] Вручную напишите оператор CREATE PROCEDURE

Если вы не знаете, как создать оператор процедуры по своему собственному дизайну, позвольте нам объяснить, чтобы помочь вам стать мастером.

Вы начинаете сценарий с CREATE PROCEDURE (всегда должно быть написано заглавными буквами).

Оттуда нажмите клавишу пробела и введите имя_процедуры.

После этого еще один пробел, затем AS.

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

 CREATE PROCEDURE GetCustomer AS 

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

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

 CREATE PROCEDURE GetCustomer @CustomerId int AS 

4] Вызов хранимой процедуры

Чтобы вызвать или выполнить хранимую процедуру, вы должны использовать либо команду EXEC, либо команду EXECUTE. Не беспокойтесь, потому что оба делают одно и то же.

Если внимательно следить, то готовый продукт должен выглядеть так, как показано ниже:

 EXEC GetCustomer @CustomerId = 7 

ИЛИ

 EXECUTE GetCustomer @CustomerId = 7 

Итак, какой номер 7 все о? Ну, переданный параметр выполнил CustomerId, который содержит 7 в качестве значения. Это означает, что если номер изменится, SQL будет обрабатывать другого клиента.

ПРОЧИТАЙТЕ : Как загрузить и установить MySQL в Windows

Является ли SQL Server бесплатным?

Существует бесплатная версия SQL Server, которая называется SQL Server 2022 Express. Он идеально подходит для разработки и производства настольных, веб-приложений и небольших серверных приложений.

Сложно ли изучить SQL Server?

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

83
Акции

• Подробнее

Опубликовано 8 февраля 2023 г. Теги: SQL

[email protected]

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