процедура с выходными параметрами sql server
Процедура с выходными параметрами sql server
Хранимая процедура может использовать не только входные параметры, через которые передаются значения в процедуру, но и выходные параметры. Выходные параметры по сути представляют результат работы процедуры, те значения, которые она возвращает пользователю.
Опять же возьмем наш базу данных и определим в ней следующую хранимую процедуру:
В этой процедуре определено три параметра, с помощью которых мы будем получать минимальный и максимальный возраст в базе данных. Через параметр @name мы будем получать имя пользователя, для которого осуществляется поиск минимального и максимального возраста. Причем в данном случае параметр по умолчанию имеет значение ‘%’. Данное значение указывает на произвольную строку. То есть если значение для этого параметра передано, то процедура будет находить возраст только для пользователей с этим именем. Если же значение для параметра не передано, то процедура будет находить минимальный и максимальный возраст всех пользователей вне зависимости от имени.
Параметры @minAge и @maxAge являются выходными благодаря указанию ключевого слова out в их определении. Через них мы собственно и будем получать минимальный и максимальный возраст.
Теперь перейдем к программе на C# и определим в ней следующий код:
Теперь мы можем ввести имя пользователя, либо просто нажать на Enter, и процедура вернет нам выходные параметры. При этом может возникнуть ситуация, что пользователя с введенным именем не окажется в системе, и тогда параметры будут содержать пустые значения.
Процедура с выходными параметрами sql server
Хранимая процедура может использовать не только входные параметры, через которые передаются значения в процедуру, но и выходные параметры. Выходные параметры по сути представляют результат работы процедуры, те значения, которые она возвращает пользователю.
Опять же возьмем наш базу данных и определим в ней следующую хранимую процедуру:
В этой процедуре определено три параметра, с помощью которых мы будем получать минимальный и максимальный возраст в базе данных. Через параметр @name мы будем получать имя пользователя, для которого осуществляется поиск минимального и максимального возраста. Причем в данном случае параметр по умолчанию имеет значение ‘%’. Данное значение указывает на произвольную строку. То есть если значение для этого параметра передано, то процедура будет находить возраст только для пользователей с этим именем. Если же значение для параметра не передано, то процедура будет находить минимальный и максимальный возраст всех пользователей вне зависимости от имени.
Параметры @minAge и @maxAge являются выходными благодаря указанию ключевого слова out в их определении. Через них мы собственно и будем получать минимальный и максимальный возраст.
Общий код добавления:
Теперь применим добавленную процедуру для получения диапазона возрастов по имени:
Здесь вначале выводим список пользователей из базы данных с помощью метода GetAllUsersAsync, а затем с помощью вызова GetAgeRangeAsync ищем диапазон возрастов по имени пользователя.
Теперь мы можем ввести имя пользователя, либо просто нажать на Enter, и процедура вернет нам выходные параметры. При этом может возникнуть ситуация, что пользователя с введенным именем не окажется в системе, и тогда параметры будут содержать пустые значения.
Выполнение хранимой процедуры
В этом разделе описывается, как выполнить хранимую процедуру 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.
На панели «Стандартная» нажмите Создать запрос.
Создание хранимых процедур
На практике часто бывает нужно получить результаты запроса для определенного значения (параметра). Такие запросы называются параметризированными, а соответствующие процедуры создаются с параметрами. Например, для получения записи в таблице «Туристы» по заданной фамилии создаем следующую процедуру:
После знака @ указывается название параметра и его тип. Мы выбрали nvarchar c количеством символов 50, поскольку в самой таблице для поля «Фамилия» установлен этот тип. Попытаемся запустить процедуру:
Появляется диагностическое сообщение (рис. 5.5):
Перевод этого сообщения: «Процедура ‘proc_p1’ ожидает параметр ‘@Фамилия’, который не указан».
Запустим процедуру так:
В результате выводится запись, соответствующая фамилии «Андреева» (рис. 5.6):
Если мы укажем фамилию, которая не содержится в таблице, появится пустая запись (рис. 5.7):
В таблице 5.2 приводятся примеры хранимых процедур с параметрами.
