- Как вывести результат процедуры sql
- Как сохранить результат хранимой процедуры в таблице? — Microsoft SQL Server
- Создание хранимой процедуры, которая возвращает табличные данные
- Способ 1 – Используем вызов процедуры в конструкции INSERT INTO
- Способ 2 – Используем связанный сервер
- Способ 3 – Используем конструкцию OPENROWSET
- Как вывести результат процедуры sql
Как вывести результат процедуры sql
6.2. Возврат результатов хранимых процедур
Процедуры могут возвращать результаты вызывающим их программным объектам одним из следующих способов:
- через фактические параметры типов OUT и INOUT ;
- путем формирования результирующего множества;
- используя оператор RETURN .
В качестве примера процедуры, возвращающей результат через параметры типа OUT , ниже приводится текст процедуры Get_number_absent. Данная процедура определяет количество книг, находящихся на руках у заданного читателя:
Для того, чтобы проверить работу процедуры Get_number_absent необходимо выполнить следующую последовательность SQL-операторов:
В результате получим, у читателя с номером читательской карточки ( N_reader) 80 на руках находятся четыре книги.
В процедуре Get_number_absent используется только один параметр тира OUT . В общем случае параметров такого типа в процедурах может быть несколько. По этой причине результат, формируемый процедурой Get_number_absent можно получить и с использованием оператора RETURN . Данный оператор возвращает в качестве результата одно значение. Для демонстрации этой возможности создадим процедуру Num_absent на базе приведенной выше процедуры Get_number_absent, путем незначительной модификации последней:
Чтобы убедиться в работоспособности процедуры Num_absent следует воспользоваться такой последовательностью SQL-операторов:
Эта процедура выдает те же данные, что и Get_number_absent.
В случае необходимости получения списка книг, выданных конкретному читателю, следует применить процедуру, формирующую результирующее множество. Процедура, решающая поставленную задачу приведена ниже:
Результирующее множество формируется оператором SELECT . Этот оператор всегда формирует такое множество, если в нем не используется отсутствует служебное слово INTO . Данное служебное слово применяется для указания переменных, в которые будут записываться результаты выполнения оператора SELECT .
О том, что в качестве результата процедуры будет формироваться результирующее множество, указывает оператор RESULT . Отсутствие этого оператора не позволит в дальнейшем воспользоваться результирующим множеством даже если оно будет сформировано.
При описании параметров оператора RESULT их количество и типы должны соответствовать количеству и типам элементов списка выбора оператора SELECT . При этом имена этих параметров и элементов могут не совпадать друг с другом. Для проверки процедуры Get_list_absent выполните ее:
Теперь проверьте полученные результаты. Они будут представлены в окне Data утилиты ISQL. Содержимое результатов представлено в табл. 23.
Таблица 23. Результаты выполнения процедуры Get_list_absent(80)
| Author | Title_book | City_pub-lish | Publisher | Year_pub-lish |
|---|---|---|---|---|
| Гмурман В.Е. | Теория вероятностей и математическая статистика.Учебное пособие для студентов ВТУЗов | Москва | Высшая школа | 1972 |
| Гмурман В.Е. | Руководство к решению задач по теории вероятностей и математической статистике.Учебное пошкола пособие для студентов ВТУЗов | Москва | Высшая школа | 1979 |
| Дектярев Ю.И. | Методы оптимизации | Москва | Советское радио | 1980 |
| Габасов Р. | Методы оптимизации | Минск | БГУ | 1981 |
При выполнении процедуры, формирующей результирующее множество, создается временная таблица — курсор ( CURSOR ). В курсор записывается результирующее множество. В дальнейшем пользователь может обрабатывать данные курсора по-своему усмотрению. Это будет обсуждаться в следующем п.5.3.
Следует обратить особое внимание на использование оператора RESUME в связи с результирующим множеством. Он продолжает выполнение процедурыв среде утилиты ISQL в связи с ее обязательной остановкой после формирования такого множества.
В среде утилиты ISQL процедура работает до своего нормального или аварийного завершения или до тех пор, пока не будет сформировано результирующее множество. Последнее возможно только, если для этого множества при помощи оператора OPEN не был открыт курсор.
Допустим, что процедура приостановила свое функционирование после формирования результирующего множества, и при этом не применялся оператор OPEN . Тогда процедура будет находиться в «зависшем» состоянии на сервере до тех пор, пока не будет выполнен оператор RESUME . Данный оператор закрывает курсор и продолжает работу процедуру до ее завершения или до формирования следующего результирующего множества.
Исходя из этого, следует отметить, что выполнение процедуры Get_list_absent не завершено. Она все еще находится в «зависшем» состоянии на сервере. Для ее завершения выполните оператор RESUME .
Покажем использование оператора RESUME на примере процедуры For_RESUME :
После вызова процедуры For_RESUME в утилите ISQL:
- В окне Data будет представлено результирующее множество, сформированное в результате выполнения в процедуре For_RESUME оператора CALL Get_list_absent(80).
- В окне Statistics будет выдано сообщение «. Procedure is executing.Use RESUME to continue.», означающее, что происходит выполнение процедуры приостановлено и для продолжения ее выполнения следует выполнить оператор RESUME .
- Выполнение процедуры будет приостановлено. Об этом свидетельствует сообщение в окне Statistics . Кроме того в окне локального сервера (см. рис. 2) или окне Messages удаленного сервера (см. рис. 4) в данной программе при помощи первого оператора MESSAGE выводится только одно сообщение «_______Parameter =80». Сообщения , формируемые тремя другим операторами MESSAGE пока не выводятся, потому что выполнение процедуры For_RESUME приостановлено.
После выполнения оператора RESUME функционирование процедуры For_RESUME будет продолжено до тех пока не будет сформировано следующее результирующее множество. В данном случае это сделает оператор SELECT .
В случае необходимости процедура завершения работы процедуры до конца следует воспользоваться оператором RESUME ALL . При этом кроме текущей завершаются также все процедуры формирующие результирующие множества, которые находятся в «подвешенном» состоянии на сервере.
После выполнения оператора SELECT происходит следующее:
- В окне Data будет представлено результирующее множество, сформированное оператором SELECT .
- В окне Statistics опять будет предложено выполнить оператор RESUME для продолжения функционирования процедуры.
- Об остановке работы процедуры свидетельствует сообщение в окне Statistics. Кроме того в окне сервера появится сообщение «____ Parameter =60«. Остановка процедуры произойдет, несмотря на то, что в данном случае результирующее множество будет пустое.
После выполнения оператора RESUME функционирование процедуры For_RESUME будет продолжено до следующего результирующее множество.
После его анализа выполним последний третий раз оператор RESUME . В результате этого получим:
- Окне Data будет пустым.
- В окне Statistics будет выдано сообщение о завершении процедуры «Procedure completed.».
- В окне сервера появится сообщение «**** FINISH «. Его формирует последний оператор процедуры For_RESUME .
Если в процедуре результирующее множество формируется несколько раз, например, как в процедуре For_RESUME , то число полей результирующего множества и их типы должны для каждого множества быть одни и теми же.
Таким образом, рассмотренные разнообразные способы получения результатов функционирования хранимых процедур придают гибкость процессу разработки приложений баз данных.
Источник
Как сохранить результат хранимой процедуры в таблице? — Microsoft SQL Server
Сегодня в материале мы с Вами рассмотрим несколько способов реализации того, как можно в Microsoft SQL Server сохранить результат выполнения хранимой процедуры в таблице в тех случаях, когда процедура возвращает табличные данные.
Это Вам может потребоваться, например, тогда когда нет возможности изменить код процедуры таким образом, чтобы непосредственно в самой процедуре осуществлялась вставка (INSERT) данных, которые она возвращает, в нужную таблицу.
Если у Вас встала подобная задача сразу скажу, что универсального способа я не нашел, каждый из перечисленных способов ниже имеет свои недостатки, иными словами, какой использовать решать Вам.
Для начала давайте создадим тестовую хранимую процедуру, которая будет возвращать табличные данные. Все действия ниже я буду выполнять на Microsoft SQL Server 2016 Express, на текущий момент вышла уже 2017 версия SQL Server, о том, что нового в ней появилось, можете почитать в материале – «Обзор основных нововведений в Microsoft SQL Server 2017».
Создание хранимой процедуры, которая возвращает табличные данные
Для примера давайте напишем простую процедуру, которая будет возвращать небольшую таблицу, состоящую всего из трех столбцов, данную таблицу я сформирую с помощью конструктора табличных значений. Для создания процедуры запускаем следующую инструкцию.
Как видите, процедура создана и возвращает табличные данные.
Способ 1 – Используем вызов процедуры в конструкции INSERT INTO
Инструкция INSERT позволяет в качестве источника указывать вызов хранимой процедуры, но у данного способа есть один очень существенный недостаток, таблица, в которую Вы хотите сохранить данные, должна уже существовать, т.е. Вы заранее должны знать количество и тип данных возвращающихся столбцов, для того чтобы создать соответствующую таблицу.
В следующем примере мы создадим временную таблицу, выполним инструкцию INSERT, в которой в качестве источника будет выступать вызов хранимой процедуры.
Способ 2 – Используем связанный сервер
В данном случае мы создаем связанный сервер, который будет ссылаться на самого себя, иными словами, на текущий сервер. Затем с помощью конструкции OPENQUERY мы обращаемся к связанному серверу, запуская на нем соответствующую процедуру. Результат в данном случае мы можем сохранять уже с помощью конструкции SELECT INTO в новую таблицу (в нашем случае для примера во временную таблицу).
Плюс данного способа в том, что Вам уже не нужно заранее создавать таблицу и соответственно знать количество столбцов. Но, как Вы понимаете, у данного способа есть и недостатки, например, Вы должны для выполнения таких процедур предварительно создать связанный сервер, также данный способ не будет работать, если табличные данные не имеют названия колонок (например, SELECT 1, 2, 3) и если в хранимой процедуре используются временные таблицы.
Способ 3 – Используем конструкцию OPENROWSET
Этот способ подразумевает использование функции OPENROWSET и поставщика OLE DB. Для использования данного способа у Вас должен быть включен параметр Ad Hoc Distributed Queries.
Плюс этого способа в том, что Вам уже не нужно предварительно создавать ни таблицу, ни связанный сервер. Но, минусы все равное есть, конструкцию использовать не получится, если в хранимой процедуре используются временные таблицы или есть неименованные столбцы, и, как я уже сказал, предварительно нужно включить параметр «Ad Hoc Distributed Queries».
Заметка! Если Вас интересует SQL и T-SQL, рекомендую посмотреть мои видеокурсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать с использованием языка T-SQL в Microsoft SQL Server.
У меня все, надеюсь, материал был Вам полезен, пока!
Источник
Как вывести результат процедуры sql
Хранимые процедуры являются еще одной формой выполнения запросов к базе данных. Но по сравнению с ранее рассмотренными запросами, которые посылаются из приложения базе данных, хранимые процедуры определяются на сервере и предоставляют большую производительность и являются более безопасными.
Объект SqlCommand имеет встроенную поддержку хранимых процедур. В частности у него определено свойство CommandType , которое в качестве значения принимает значение из перечисления System.Data.CommandType . И значение System.Data.CommandType.StoredProcedure как раз указывает, что будет использоваться хранимая процедура.
Но чтобы использовать хранимые процедуры, нам надо их вначале создать. Для этого перейдем в SQL Server Management Studio к нашей базе данных usersdb, раскроем ее узел и далее выберем Programmability->Stored Procedures. Нажмем на этот узел правой кнопкой мыши и в контекстном меню выберем пункт Stored Procedure. :
В центральной части программы открывает код процедуры, который генерируется по умолчанию. Заменим этот код следующим:
Эта процедура выполняет добавление данных. После выражения CREATE PROCEDURE идет название процедуры. Процедура называется «sp_InsertUser», и по этому названию мы ее будем вызывать в коде C#. Название процедуры может быть любое.
Процедура имеет два входных параметра: @name и @age. Через эти параметры будут передаваться значения для имени и возраста пользователя. В самом теле процедуры после выражения AS идет стандартное sql-выражение INSERT, которое выполняет добавление данных. И в конце с помощью выражения SELECT возвращается результат. Выражение SCOPE_IDENTITY() возвращает id добавленной записи, поэтому на выходе из процедуры мы получим id новой записи. И завершается процедура ключевым словом GO.
И затем нажмем на кнопку Execute. После этого в базу данных добавляется хранимая процедура.
Подобным образом добавим еще одну процедуру, которая будет возвращать объекты:
И также для ее добавления нажмем на кнопку Execute.
Теперь перейдем к коду C# и определим следующую программу:
Для упрощения кода обращения к процедурам здесь вынесены в отдельные методы. В методе AddUser вызывается процедура sp_InsertUser. Ее название передается в конструктор объекта SqlCommand также, как и обычное sql-выражение. И с помощью выражения command.CommandType = System.Data.CommandType.StoredProcedure устанавливается, что это выражение система будет рассматривать как хранимую процедуру.
Поскольку процедура получает данные через параметры, то соответственно нам надо определить эти параметры с помощью объектов SqlParameter . Ему передается название параметра и значение. Названия параметров должны соответствовать тем названиям, которые мы определили в коде процедуры.
С помощью метода command.Parameters.Add() параметры добавляются к процедуре. И затем происходит выполнение.
Так как в коде процедуры добавления мы определили возвращение id новой записи, то есть возвращение скалярного значения, то для выполнения команды и его получения мы можем использовать метод ExecuteScalar() . Но мы также можем использовать и метод ExecuteNonOuery() , только он вернет количество добавленных записей, а не id.
В случае второго метода все еще проще: объекту команды просто передается название процедуры, и так как процедура фактически выполняет выражение SELECT и возвращает набор данных, то для выполнения команды мы можем использовать метод ExecuteReader() . И с помощью ридера получить все данные.
Запустим программу и введем какие-либо данные на добавление:
Источник
