Отримання даних з збереженої процедури

SQL Server пропонує чотири способи отримання даних з збереженої процедури Пакет може повернути дані з інструкції SELECT або команди raiseerror Збережені процедури використовують ці методи пакетів і додають до них два власних: вихідні змінні і команду output

Вихідні параметри

Вихідні параметри дозволяють збереженій процедурі повертати дані викликає клієнтській програмі При цьому вказівка ​​ключового слова output обовязково як при визначенні процедури, так і при її виклику У самій збереженій процедурі вихідні параметри є локальними змінними У викликає процедурою або пакеті вихідні змінні повинні бути попередньо визначені, щоб отримати результуючі значення Коли виконання збереженої процедури завершується, поточне значення параметра передається локальної змінної викликає програми

Незважаючи на те що вихідні параметри зазвичай використовуються тільки для виведення значень, насправді вони мають подвійне призначення

Вихідні параметри зазвичай використовують, коли з збереженої процедури потрібно отримати одну інформаційну одиницю, а не набір даних Для повернення одного рядка інформації набагато вигідніше в сенсі швидкодії використовувати вихідні параметри, ніж підготовляти набір даних

У наступному прикладі вихідний параметр використовується для повернення назви конкретного товару з таблиці Product бази даних OBXKites

1&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp У пакеті визначається локальна змінна @ ProdName, яка отримує значення вихідного параметра

2&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp У пакеті викликається збережена процедура із зазначенням імені вихідного параметра @ ProdName

3&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp У збереженій процедурі локальна змінна @ ProdName, відповідна переданим вихідному параметру, створюється в заголовку її споконвічне значення дорівнює null

Тепер слід підготувати дані для вихідного параметра @ ProductName

4&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Інструкція SELECT в тілі процедури, що зберігається привласнює змінної @ ProductName значення Basic Box Kite 21 inch, відповідне товару з кодом 1 жовтня

5&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Збережена процедура завершує виконання, і управління передається зухвалому пакету при цьому отримане значення @ ProductName передається його однойменної локальної змінної

6&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Зухвалий пакет за допомогою команди print виводить отримане значення на екран Нижче наведено текст збереженої процедури

USE OBXKites go

CREATE PROC GetProductName (

@ProductCode CHAR(10),

@ProductName VARCHAR(25) OUTPUT )

AS

SELECT @ProductName = ProductName FROM dboProduct WHERE Code = @ProductCode

А це текст викликає пакету:

USE OBXKITES

DECLARE @ProdName VARCHAR(25)

EXEC GetProductName 1001, @ProdName OUTPUT

PRINT @ProdName

Буде отримано наступний результат:

Basic Box Kite 21 Inch

Пізніше в цій главі буде приведений складніший приклад, взятий з бази даних OBXKites У ньому вихідний параметр буде використаний для передачі номера замовлення з однієї збереженої процедури в іншу

Використання команди return

Команда return дозволяє завершити виконання процедури в її середині і повернути значення зухвалому пакету або клієнту З технічної точки зору ця команда може використовуватися при роботі з будь-яким пакетом, проте значення вона може повернути тільки з збереженої процедури або функції

Значення, що повертається 0 вказує на успішне виконання процедури та встановлено за умовчанням Компанія Microsoft зарезервувала значення від -99 до -1 для службового користування Розробникам для повернення стану помилки користувачеві рекомендується використовувати значення -100 і менше

Використовуйте повертається значення для індикації успішного або аварійного завершення процедури Намагайтеся не використовувати його для повернення реальних даних Якщо вам потрібно отримати не набір даних, а всього одне значення, краще скористайтеся вихідним параметром

При виклику процедури, що зберігається, якщо очікується вихідне значення, команда Ехес повинна використовувати целочисленную змінну:

Ехес @ локальная_переменная = імя_хранімой_процедури

У наступному прикладі збережена процедура повертає індикатор успішного або аварійного завершення залежно від значення вхідного параметра:

CREATE PROC IsItOK (

@ ОК VARCHAR (10))

AS

IF @0K = OK1 RETURN 0 ELSE

RETURN -100

А ось приклад зухвалого пакету:

DECLARE @ReturnCode INT

EXEC @ReturnCode = IsITOK OK;

PRINT @ReturnCode

EXEC @ReturnCode = IsItOK NotOK;

PRINT @ReturnCode

Результати виконання пакету будуть наступними:

про

-100

Маршрути і область визначення повертаються даних

Будь збережена процедура має чотири способи повернення даних (SELECT, raiseerror, return і вихідні параметри) Рішення щодо використання одного з цих методів приймається на підставі кількості і призначення повертаються даних, а також області їх визначення Область визначення можливих чотирьох методів наступна

■ Команда return і вихідні параметри передають значення локальним змінним викликає пакета

■ Команда raiserror і результуючий набір даних передаються клієнтського додатку Сама викликає процедура або пакет нічого не знає про ці значеннях

Якщо пакет, що викликає збережену процедуру А, яка, в свою чергу, викликає збережену процедуру Б, виконується в аналізаторі запитів, то збережена процедура А не побачить набору даних або помилки raiserror, повернутих процедурою Б (рис 212)

Рис 212 Маршрути і область визначення повертаються даних залежать від використовуваного методу

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

На рис 212 процедура Б може виконати будь-які інструкції DML щодо будь тимчасової таблиці, створеної процедурою А Після завершення процедури Б ці дані стануть доступні процедурі А

rv З будь-яким повертаним набором даних SQL Server за замовчуванням буде по-

vcZL силать повідомлення про кількість оброблених або повернутих рядків Це

^ ^ Й / Ь всього лише невеликий нюанс, але при неформальному тестуванні я вияв-

жив> що це уповільнює виконання запиту на 17%

Перевірено Виходячи з цього, виробіть звичку починати будь-яку збережену процедуру такими рядками:

AS

SET NoCount ON

Більш повну інформацію про конфігуруванні підключень ви отримаєте у главі 34

Використання збережених процедур в запитах

Збережені процедури зазвичай запускаються на виконання за допомогою команди Ехес з пакету або клієнтського додатку У той же час їх можна викликати і в реченні FROM запиту, використовуючи при цьому функцію openquery ()

Функція Openquery () посилає наскрізний запит зовнішнього джерела даних для віддаленого виконання Якщо у функції openquery () вказана збережена процедура, то її результати передаються на локальний сервер

Додаткова Детально про функції openquery () см в главі 15

інформація

Так як результуючий набір даних збереженої процедури повертається функцією і використовується як джерело даних в реченні FROM інструкції SELECT, пропозиція WHERE може скоротити обсяг виведення збереженої процедури

Незважаючи на те що цей прийом дозволяє використовувати збережені процедури в інструкціях SELECT, він не так оптимізований, як техніка передачі будь-яких обмежень для обробки збереженої процедурою Єдиним гідністю функції openquery () є те, що вона дозволяє викликати складну процедуру, що зберігається з будь-якого запиту

У наступному прикладі передбачається, що було встановлено підключення до повязаного сервера NOLI:

SELECT * FROM OpenQuery(

NOLI

‘EXEC OBXKitesdbopProductCategory_Fetch)

WHERE ProductCategoryDescription Like %stuff%

Результат виконання запиту:

ProductCategoryName ProductCategoryDescription

OBX                              OBX stuff

Toy                                Kids stuff

Якщо вам потрібно викликати складний програмний код з інструкції SELECT, то використання функції openqueryO для виклику збереженої процедури спрацює, проте синтаксис буде занадто складний Краще для цих цілей створити вираз CASE або користувача функцію

Джерело: Нільсен, Пол Microsoft SQL Server 2005 Біблія користувача : Пер з англ – М: ООО ІД Вільямс , 2008 – 1232 с : Ил – Парал тит англ

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*