Выводится единственная строка, поскольку результирующий набор оператора SELECT

передается переменной, способной хранить только одно значение.

Эта проблема имеет несколько imii. Простейшее заключается в отказе от использования параметров в операторе SELECT и возврате при исполнении процедуры набора записей, как показано ниже:

ALTER PROCEDURE dbo.SalesForTitle

iTltle varchar(80)

SELECT Title = title, [Number of Sales]=ytd sales

EROM titles WHERE title LIKE ©Title

Обратите внимание: из хранимой процедуры удалены все выходные параметры. Эту простую хранимую процедуру можно исполнить с помощью кода:

FXFCUTE SalesForTitle №tle = The%

При исполнении эта процедура возвращает результирующий набор, показанный в следующей таблице.

Для простоты из хранимое цедуры удалены фрагменты с кодами мрата, поэтому столбец Return Code отсутствует. В результирующем наборе перечислены все книги, заглавие которых начинается с .The% .

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

Курсоры

В хранимых процедурах разрешается использовать курсоры. Однако этого следует избегать, если есть возможность выполнить ту же самую задачу посредством результирующих наборов. Процедуры, использующи.; результирующие наборы, более эффективны, и, как правило, их проще писать, че дуры, использующие курсоры. Подробно о курсорах рассказано в занятии 3 главы 7, а они обсуждаются в контексте их применения в хранимых процедурам. Вы также найдете сведения о курсорах в SQL Server Books Online, a также в книге Inside Microsoft SQL Server 2000 , изданной Microsoft Press.

Методы извлечения данных

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

В этом упражнении мы займемся созданием хранимой процедуры для добавления в базу данных новых записей о покупателях. После этого мы созда-

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

► Создание пользовательской хранимой процедуры:

1. Откройте Query Analyzer и подключитесь к локальному серверу.

2. На панели Editor в окне Query введите следующий код:

USE BookShopDB

CREATE PROCEDURE dbo. AddCustomer

TifrTD не входит в параметры ппьку идентификатор - генерируется автоматически (в столбце с идентификатором)

ALTER PROCEDURE dbo,SalesForTitle

giResultCrS- CURSOR VARYING OUTPUT, ©Title varchar(80) = NULL

SET ©ResultCrsr = CURSOR FOR SELECT vtd sales, title FROM dbo. titles WHERE title LIKE @Title OPEN ©ResultCrsr

Во второй строке программы создается выходной параметр ©ResultCrsr, для которого определен тип данных cursor. Выходной параметр сохраняет каждую строку, которую

курсор. После ключевого слова AS параметру присваивается значе-

ние курсора. В конечном счете, этот параметр получит все записи, которые вернул

тор SELECT. Оператор OPEN, стоящий после оператора SELECT, заполняет курсор ре-

зулътируюшим набором.

Хранимую процедуру исполняет следующий фрагмент кода:

DECLARE uitCisr CURSOR EXECUTE dbo. SalesForTitle

©ResultCrsr = @r ResultCrsr OUTPUT, csTltle = There FETCH NEXT FROM @r ResultCrsr WHIL H STATLIS <> -1) BEGIN

FETCH NEXT FROM @r F!esultCrsr END

CLOSE ©rJesultCrsr DEALLOCATE 0r ResultCr3r GO

Возвращаются все три записи из базы данных. Выводимые данные

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

Упражнение 3. Программирование хранимой процедуры для добавления и извлечения данных

;26 e процвдары Глава 3

FirstNarne varchar(30), @LastNanH; varchar(30),

@Phone varchar(24), @Address1 varchar(60),

@Address2 varchar{60) ж. fflClty varchar(15),

©State varchar(7), iZlp varch3r(12)

INSERT [BookShopDB].[dbo].[Customers] (FirstName, LastName Phone, Addressi, AddressZ, City. State, Zip) VALUES

(©FirstName, eLastName, @Phon3. giAddressI, ®Adctress2, ©City, @State, @Zip)

RETURN(SELEC NTITY AS Identity)

Этот оператор мдает хранимую Add Customer. Здесь определяются все необходимые входные параметры. Обратите внимание на значение own*, заданное по умолчанию для параметра Параметр не всегда является частью

адреса покупателя. Значение unknown соответствует значению по опре-

деленному в ограничении CHECK для этого поля. Оператор RETURN, в конце процедуры, содержит значение идентификатора покупателя, которое извлекает функция @@[DENTITY. 3. На панели Editor в окне Query и исполните следующий код:

DECLARE Code int

EXECUTE @r Code=db0.AddCuStom3r

@FirstName ©LastNaite = Marra,

@Phone = А1Ь-ЬЬ5-ШГ . ©Add-esst = 20 Oak St, , SE,

City = Remy, ©State = WA, iPZip = 98888

SELECT [Customer ID] = The nsw customer ID is: +

C0NVERT(CHAR(2), fflr.Code)

Программа создает целочисленную переменную @r Code. При исполнении процедуры

переменной присваивается значение, возвращаемое процедурой. Образцы процедур из

занятия 3 иллюстрируют использование кодов возврата для обработки ошибок, При исполнении этой процедуры код возврата хранит значение

Добавление к хранимой процедура нтма обработки ошибок

1. В хранимой процедуре AddCustomer отсутствует обработка ошибок. Следует модифицировать эту процедуру, команды обработки ошибок. Для этого на панели Editor в окне Query введите и исполните следующий код:

ALTER PROCEDURE dbo,AddCustomar

igfiiSLNane varC[iar(30)= ипкизчп,

©LastName varchar(30)= unkno.vn, ©Phone varchar(24) = NULL, @Address1 varchar(6G) = NULL,

@Address2 varchar(60) - unknown. ©City varchar(15) = NULL,

estate varchar(7) = NULL, @71л уагсагг) = NULL

IF vgFirstName = unknown) AW (@LastName = unknown)

RETURN(1)

ELSE IF ®Phone IS NULL