ms sql создать процедуру с параметром
Создание хранимой процедуры
В этом разделе описывается, как можно создать хранимую процедуру Transact-SQL в среде SQL Server Management Studio с использованием инструкции Transact-SQL CREATE PROCEDURE.
Перед началом работы Разрешения
Permissions
Для выполнения этой инструкции требуется разрешение CREATE PROCEDURE в отношении базы данных и разрешение ALTER в отношении схемы, в которой создается процедура.
Создание хранимой процедуры
Можно использовать один из следующих способов:
Использование среды SQL Server Management Studio
Создание процедуры в обозревателе объектов
В обозревателе объектов подключитесь к экземпляру компонента Компонент Database Engine и разверните его.
Последовательно разверните узел Базы данных, базу данных AdventureWorks2012 и узел Программирование.
Щелкните правой кнопкой мыши элемент Хранимые процедуры и выберите пункт Создать хранимую процедуру.
В меню Запрос выберите пункт Указать значения для параметров шаблона.
В диалоговом окне Задание значений для параметров шаблона введите для показанных параметров следующие значения.
| Параметр | Значение |
|---|---|
| Автор | Ваше имя |
| Дата создания | Сегодняшняя дата |
| Описание | Возвращает данные о сотрудниках. |
| Procedure_name | HumanResources.uspGetEmployeesTest |
| @Param1 | @LastName |
| @Datatype_For_Param1 | nvarchar(50) |
| Default_Value_For_Param1 | NULL |
| @Param2 | @FirstName |
| @Datatype_For_Param2 | nvarchar(50) |
| Default_Value_For_Param2 | NULL |
Нажмите кнопку ОК.
В редакторе запросов замените инструкцию SELECT следующей инструкцией:
Для проверки синтаксиса выберите пункт Выполнить анализ в меню Запрос. Если возвращается сообщение об ошибке, сравните инструкции с приведенными выше и при необходимости внесите исправления.
Чтобы создать процедуру, в меню Запрос выберите пункт Выполнить. Процедура создается как объект в базе данных.
Чтобы увидеть процедуру в обозревателе объектов, щелкните правой кнопкой мыши элемент Хранимые процедуры и выберите пункт Обновить.
Чтобы выполнить процедуру, в обозревателе объектов щелкните правой кнопкой мыши имя хранимой процедуры HumanResources.uspGetEmployeesTest и выберите пункт Выполнение хранимой процедуры.
В окне Выполнение процедуры введите Margheim в качестве значения для параметра @LastName и Diane в качестве значения для параметра @FirstName.
Проверяйте все данные, вводимые пользователем. Не включайте их в сценарий, не выполнив проверку. Никогда не выполняйте команду, построенную на основании непроверенных пользовательских входных данных.
Использование Transact-SQL
Создание процедуры в редакторе запросов
В обозревателе объектов подключитесь к экземпляру компонента Компонент Database Engine.
В меню Файл выберите пункт Создать запрос.
Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить. В данном примере создается та же хранимая процедура, что и раньше, но с другим именем процедуры.
Чтобы выполнить процедуру, скопируйте следующий пример в окно создаваемого запроса и нажмите кнопку Выполнить. Показаны различные методы задания значений параметров.
Выполнение хранимой процедуры
В этом разделе описывается, как выполнить хранимую процедуру SQL Server при помощи среды SQL Server Management Studio или Transact-SQL.
В этом разделе
Перед началом работы
Для выполнения хранимой процедуры используется:
Перед началом
Ограничения
При сопоставлении имен системных процедур используются параметры сортировки вызывающей базы данных. Таким образом, в вызове процедур следует всегда использовать точный регистр имен системных процедур. Например, этот код завершится с ошибкой при выполнении в контексте базы данных, в параметрах сортировки которой учитывается регистр:
Если определяемая пользователем процедура имеет имя, совпадающее с системной процедурой, то такая определяемая пользователем процедура никогда не будет выполняться.
Рекомендации
Выполнение системных хранимых процедур
Выполнение пользовательских хранимых процедур
При выполнении определяемой пользователем процедуры рекомендуется дополнительно указывать имя схемы. Это позволяет немного увеличить производительность, поскольку компоненту Компонент Database Engine не нужно выполнять поиск в нескольких схемах. Также исключается выполнение неправильной процедуры в случае, если в нескольких схемах базы данных имеются процедуры с одним именем.
В следующем примере демонстрируется рекомендуемый метод выполнения определяемой пользователем процедуры. Обратите внимание, что процедура принимает один входной параметр. Сведения об указании входных и выходных параметров см. в статье Указание параметров.
Если не указано уточненное имя определяемой пользователем процедуры, компонент Компонент Database Engine производит поиск процедуры в следующем порядке.
схема sys текущей базы данных;
Схема по умолчанию вызывающей программы при выполнении в пакете или в динамическом коде SQL. Если неуточненное имя процедуры присутствует в тексте определения другой процедуры, в следующую очередь выполняется поиск в схеме, содержащей другую процедуру.
Схема dbo в текущей базе данных.
Автоматическое выполнение хранимых процедур
Ограничений на количество автоматически запускаемых процедур не существует, однако помните, что для выполнения каждой необходим один рабочий поток. Если необходимо выполнить несколько процедур при запуске, которые не должны выполняться параллельно, настройте одну процедуру на автоматический запуск, а вторую вызывайте в ее теле (в конце). Таким образом будет задействован только один рабочий поток.
Установка, очистка и контроль автоматического выполнения
Используйте процедуру sp_procoption чтобы:
обозначить существующую процедуру как автоматически запускаемую;
безопасность
Permissions
Дополнительные сведения см. в разделе «Разрешения» статьи EXECUTE (Transact-SQL).
Использование среды SQL Server Management Studio
Выполнение хранимой процедуры
В обозревателе объектов подключитесь к экземпляру компонента Компонент SQL Server Database Engine, разверните его, а затем разверните узел Базы данных.
Разверните нужную базу данных, разверните узлы Программирование и Хранимые процедуры.
Щелкните правой кнопкой мыши определяемую пользователем хранимую процедуру и выберите команду Выполнить хранимую процедуру.
В диалоговом окне Выполнение процедуры укажите значение для каждого параметра и необходимость передачи значения NULL.
Параметр
Указывает имя параметра.
Тип данных
Указывает тип данных параметра.
Выходной параметр
Указывает, является ли этот параметр выходным.
Передать значение NULL
Передать значение NULL в качестве значения параметра.
Значение
Введите значение параметра, передаваемое ему при вызове процедуры.
Чтобы выполнить хранимую процедуру, нажмите кнопку ОК.
Использование Transact-SQL
Выполнение хранимой процедуры
Установите соединение с компонентом Компонент Database Engine.
На панели «Стандартная» нажмите Создать запрос.
Установка и отмена автоматического запуска процедуры
Установите соединение с компонентом Компонент Database Engine.
На панели «Стандартная» нажмите Создать запрос.
Отмена автоматического выполнения процедуры
Установите соединение с компонентом Компонент Database Engine.
На панели «Стандартная» нажмите Создать запрос.
Указание параметров
Путем указания параметров процедуры вызывающие программы могут передавать значения в тело процедуры. Эти значения могут использоваться для разных целей во время исполнения процедуры. Параметры процедуры могут также возвращать значения вызывающей программе, если параметр помечен признаком OUTPUT.
Хранимая процедура может иметь не более 2100 параметров, каждый из которых имеет имя, тип данных и направление. При необходимости параметрам можно задавать значения по умолчанию.
В следующем разделе содержатся сведения о передаче значений параметрам и о том, как каждый из атрибутов параметров используется во время вызова процедуры.
Передача значений в параметры
Значения параметра, переданные при вызове процедуры, должны быть константами или переменными. Имя функции не может быть значением параметра. Переменные могут быть пользовательскими или системными, например @@spid.
Указание имен параметров
При создании процедуры и объявлении имени параметра, последнее должно начинаться с единичного символа @ и быть уникальным для всей процедуры.
При указании одного значения параметра в формате @parameter = value необходимо точно так же предоставить все последующие параметры. Если значения параметра передаются не в формате @parameter = value, значения должны передаваться в том порядке (слева направо), в котором они перечислены в инструкции CREATE PROCEDURE.
Передача параметров в формате @parameter = value с ошибками приведет к возникновению ошибки SQL Server и невозможности выполнения процедуры.
Указание типов данных параметров
Параметры должны быть определены с типом данных в момент объявления в инструкции CREATE PROCEDURE. Тип данных параметра определяет тип и диапазон допустимых значений параметра при вызове процедуры. Например, параметр типа tinyint может принимать только численные значения в диапазоне от 0 до 255 в момент передачи этому параметру. При попытке выполнить процедуру со значением, не совместимым с типом данных, происходит ошибка.
Указание значений параметра по умолчанию
Параметр считается необязательным, если он имеет значение по умолчанию при объявлении. Нет необходимости указывать значение необязательного параметра при вызове процедуры.
Значение параметра по умолчанию используется, когда:
не указано значение для параметра при вызове процедуры.
в качестве значения при вызове процедуры указывается ключевое слово DEFAULT.
Если значением по умолчанию является символьная строка, включающая в себя знаки пробела или пунктуации, либо содержащая первым элементом число, например 6ххх, то ее следует заключить в одинарные прямые кавычки.
Параметры по умолчанию не поддерживаются для Azure Synapse Analytics и Parallel Data Warehouse.
Если значение по умолчанию указать нельзя, укажите NULL. Желательно, чтобы процедура возвращала сообщение, если она выполняется без значения для параметра.
Следующий пример выполняет процедуру. Первая инструкция выполняет процедуру без указания входного значения. В результате чего инструкции обработки ошибок процедуры возвращают пользовательское сообщение об ошибке. Вторая инструкция задает входное значение и возвращает ожидаемый результирующий набор.
Хотя разрешается опустить параметры, для которых предоставлены значения по умолчанию, можно лишь подвергнуть усечению список параметров. Например, если у процедуры пять параметров, можно опустить как четвертый, так и пятый параметр. При этом нельзя пропустить четвертый параметр, если включен пятый, если только параметры не передаются в формате @parameter = value.
Указание направления параметров
Параметр может быть как входным, когда значение передается в тело процедуры, так и выходным, возвращаемым процедурой вызывающей программе. По умолчанию параметр определен как входной.
Для указания выходного параметра в определении процедуры необходимо указать ключевое слово OUTPUT в инструкции CREATE PROCEDURE. Процедура, завершая свою работу, возвращает текущее значение выходного параметра в вызывающую программу. При выполнении процедуры вызывающая программа также должна использовать ключевое слово OUTPUT для сохранения значения параметра в переменной, которое затем может быть использовано в вызывающей программе.
Переменная OUTPUT должна быть определена во время создания процедуры, а также в ходе использования переменной. Имена параметра и переменной не должны совпадать. При этом тип данных и положение параметра должны быть одинаковыми (если только не используется @listprice= переменная).
Хранимые процедуры в T-SQL — создание, изменение, удаление
В Microsoft SQL Server для реализации и автоматизации своих собственных алгоритмов (расчётов) можно использовать хранимые процедуры, поэтому сегодня мы с Вами поговорим о том, как они создаются, изменяются и удаляются.
Но сначала немного теории, чтобы Вы понимали, что такое хранимые процедуры и для чего они нужны в T-SQL.
Примечание! Начинающим программистам рекомендую следующие полезные материалы на тему T-SQL:
Что такое хранимые процедуры в T-SQL?
Хранимые процедуры – это объекты базы данных, в которых заложен алгоритм в виде набора SQL инструкций. Иными словами, можно сказать, что хранимые процедуры – это программы внутри базы данных. Хранимые процедуры используются для сохранения на сервере повторно используемого кода, например, Вы написали некий алгоритм, последовательный расчет или многошаговую SQL инструкцию, и чтобы каждый раз не выполнять все инструкции, входящие в данный алгоритм, Вы можете оформить его в виде хранимой процедуры. При этом, когда Вы создаете процедуру SQL, сервер компилирует код, а потом, при каждом запуске этой процедуры SQL сервер уже не будет повторно его компилировать.
Для того чтобы запустить хранимую процедуру в SQL Server, необходимо перед ее названием написать команду EXECUTE, также возможно сокращенное написание данной команды EXEC. Вызвать хранимую процедуру в инструкции SELECT, например, как функцию уже не получится, т.е. процедуры запускаются отдельно.
В хранимых процедурах, в отличие от функций, уже можно выполнять операции модификации данных такие как: INSERT, UPDATE, DELETE. Также в процедурах можно использовать SQL инструкции практически любого типа, например, CREATE TABLE для создания таблиц или EXECUTE, т.е. вызов других процедур. Исключение составляет несколько типов инструкций таких как: создание или изменение функций, представлений, триггеров, создание схем и еще несколько других подобных инструкций, например, также нельзя в хранимой процедуре переключать контекст подключения к базе данных (USE).
Хранимая процедура может иметь входные параметры и выходные параметры, она может возвращать табличные данные, может не возвращать ничего, только выполнять заложенные в ней инструкции.
Хранимые процедуры очень полезны, они помогают нам автоматизировать или упростить многие операции, например, Вам постоянно требуется формировать различные сложные аналитические отчеты с использованием сводных таблиц, т.е. оператора PIVOT. Чтобы упростить формирование запросов с этим оператором (как Вы знаете, у PIVOT синтаксис достаточно сложен), Вы можете написать процедуру, которая будет Вам динамически формировать сводные отчеты, например, в материале «Динамический PIVOT в T-SQL» представлен пример реализации данной возможности в виде хранимой процедуры.
Примеры работы с хранимыми процедурами в Microsoft SQL Server
Исходные данные для примеров
Все примеры ниже будут выполнены в Microsoft SQL Server 2016 Express. Для того чтобы продемонстрировать, как работают хранимые процедуры с реальными данными, нам нужны эти данные, давайте их создадим. Например, давайте создадим тестовую таблицу и добавим в нее несколько записей, допустим, что это будет таблица, содержащая список товаров с их ценой.
Данные есть, теперь давайте переходить к созданию хранимых процедур.
Создание хранимой процедуры на T-SQL – инструкция CREATE PROCEDURE
Хранимые процедуры создаются с помощью инструкции CREATE PROCEDURE, после данной инструкции Вы должны написать название Вашей процедуры, затем в случае необходимости в скобочках определить входные и выходные параметры. После этого Вы пишите ключевое слово AS и открываете блок инструкций ключевым словом BEGIN, закрываете данный блок словом END. Внутри данного блока Вы пишите все инструкции, которые реализуют Ваш алгоритм или какой-то последовательный расчет, иными словами, программируете на T-SQL.
Для примера давайте напишем хранимую процедуру, которая будет добавлять новую запись, т.е. новый товар в нашу тестовую таблицу. Для этого мы определим три входящих параметра: @CategoryId – идентификатор категории товара, @ProductName — наименование товара и @Price – цена товара, данный параметр будет у нас необязательный, т.е. его можно будет не передавать в процедуру (например, мы не знаем еще цену), для этого в его определении мы зададим значение по умолчанию. Эти параметры в теле процедуры, т.е. в блоке BEGIN…END можно использовать, так же как и обычные переменные (как Вы знаете, переменные обозначаются знаком @). В случае если Вам нужно указать выходные параметры, то после названия параметра указывайте ключевое слово OUTPUT (или сокращённо OUT).
В блоке BEGIN…END мы напишем инструкцию добавления данных, а также в завершении процедуры инструкцию SELECT, чтобы хранимая процедура вернула нам табличные данные о товарах в указанной категории с учетом нового, только что добавленного товара. Также в этой хранимой процедуре я добавил обработку входящего параметра, а именно удаление лишних пробелов в начале и в конце текстовой строки с целью исключения ситуаций, когда случайно занесли несколько пробелов.
Вот код данной процедуры (его я также прокомментировал).
Запуск хранимой процедуры на T-SQL – команда EXECUTE
Запустить хранимую процедуру, как я уже отмечал, можно с помощью команды EXECUTE или EXEC. Входящие параметры передаются в процедуры путем простого их перечисления и указания соответствующих значений после названия процедуры (для выходных параметров также нужно указывать команду OUTPUT). Однако название параметров можно и не указывать, но в этом случае необходимо соблюдать последовательность указания значений, т.е. указывать значения в том порядке, в котором определены входные параметры (это относится и к выходным параметрам).
Параметры, которые имеют значения по умолчанию, можно и не указывать, это так называемые необязательные параметры.
Вот несколько разных, но эквивалентных способов запуска хранимых процедур, в частности нашей тестовой процедуры.
Изменение хранимой процедуры на T-SQL – инструкция ALTER PROCEDURE
Внести изменения в алгоритм работы процедуры можно с помощью инструкции ALTER PROCEDURE. Иными словами, для того чтобы изменить уже существующую процедуру, Вам достаточно вместо CREATE PROCEDURE написать ALTER PROCEDURE, а все остальное изменять по необходимости.
Допустим, нам необходимо внести изменения в нашу тестовую процедуру, скажем, параметр @Price, т.е. цену, мы сделаем обязательным, для этого уберём значение по умолчанию, а также представим, что у нас пропала необходимость в получении результирующего набора данных, для этого мы просто уберем инструкцию SELECT из хранимой процедуры.
Удаление хранимой процедуры на T-SQL – инструкция DROP PROCEDURE
В случае необходимости можно удалить хранимую процедуру, это делается с помощью инструкции DROP PROCEDURE.
Например, давайте удалим созданную нами тестовую процедуру.
При удалении хранимых процедур стоит помнить о том, что, если на процедуру будут ссылаться другие процедуры или SQL инструкции, после ее удаления они будут завершаться с ошибкой, так как процедуры, на которую они ссылаются, больше нет.
У меня все, надеюсь, материал был Вам интересен и полезен, пока!
Создание хранимых процедур
На практике часто бывает нужно получить результаты запроса для определенного значения (параметра). Такие запросы называются параметризированными, а соответствующие процедуры создаются с параметрами. Например, для получения записи в таблице «Туристы» по заданной фамилии создаем следующую процедуру:
После знака @ указывается название параметра и его тип. Мы выбрали nvarchar c количеством символов 50, поскольку в самой таблице для поля «Фамилия» установлен этот тип. Попытаемся запустить процедуру:
Появляется диагностическое сообщение (рис. 5.5):
Перевод этого сообщения: «Процедура ‘proc_p1’ ожидает параметр ‘@Фамилия’, который не указан».
Запустим процедуру так:
В результате выводится запись, соответствующая фамилии «Андреева» (рис. 5.6):
Если мы укажем фамилию, которая не содержится в таблице, появится пустая запись (рис. 5.7):
В таблице 5.2 приводятся примеры хранимых процедур с параметрами.
