Занятие 3 Программириие хранимш проьхедр 259

No matching titles in the database. Return code=1

Если изменить значение параметра на и исполнить еще

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

Если не ввести значение адного параметра ©Title, в результате исполнения запроса

будет показано следующее сообщение:

Server; Msg 201, Level 16, State 3, Procedure SalesForTitle, Line 0 procedure SalesForTitle expects parameter ©Title, which was not supplied.

Значения по умолчанию и присвоение параметру значения NULL

Чтобы проверить, задано ли значение входного параметра необходимо установить

него значение по умолчанию и модифицировать хранимую добавив к ней

еще один условный оператор. Значение по умолчанию адное средство, позволяющее избегать ошибок. Если вы не установите значение параметра @ТШе, оператор SEEECT использует его значение по умолчанию. В этом случае лучше задать значение по умолчанию для входного параметра равным NULL, а в обработке кода возврата подсказать пользователю, как правильно исполнять Для этого нужно модифицировать

процедуру следующим образом:

ALTER PROCEDURE Qbo.SalesForTitle

mtle varcnarOO) = NULL, YtdSales int OUTPUT,

@TitleText varcnar(ao) OUTPUT

IF ©Title IS NOLL RETURN(2)

ELSE

IF (SELECT C0UNT(.) FROM titles WHERE title LIKE ©Title) = 0

RETURN(1) ELSE

SELECT .YtdSales - ytdsales, isiTitleText=tltle FROM titles WHERE title LIKE ©Title

Обратите внимание: параметру ©Title присвоено значение по умолчание ULL. Сразу

после ключевого слова AS добавлен условный оператор IF. Проверка условия должна выполняться перед исполнением оператора SELECT COUNT, поскольку в противном случае при проверке условия получится TRUE, даже если значение параметра равно NULL.

При исполнении этой процедуры необходимо включить обработку кода возврата, равного 2, как показано в следующем примере:

DECLARE Ша1е:= in, @t TitleTex аг(80), @r Code int

EXECUTE :ar Code = SalesForTitle

PYtdSales = @y YtdSales OUTPUT,

@TitleText = @t TitleText OUTPUT

-- Значение параметра tle не задано.

IF @r Code = 0

102061

260 Хранимые процедуры Глава 8

SELECT Title = @t TitleText,

Number of Sales = @y YtciSales, -Returr code = @r Code ELSE IF @r Code = 1

PRINT No matching titles in the database. Return code=- + CONVERT(varchar(1),@r Code)

ELSE Code = 2 -test for a return code of 2.

PRINT You must add a value for @Title for this procedure to function properly. Return code= + C0NVERf(varchar(1),@ir Code)

Проверка наличия ошибок сервера

Другая важная категория ошибок, которые необходимо обнаружить, - базы дан-

ных. функция @@ERROR позволяет выявить более 3000 различных ошибок базы данных.

Она фиксирует номера ошибок по мере исполнения процедуры. Каждый номер, который

возвращает функция, можно для вывода раскрывающего пользо-

вателю причины ошибки. В операторах INSERT и UPDATE часто допускают пользовательские ошибки, которые к возникновению ошибок базы данных. Как правило, в этих операторах пользователь пытается ввести информацию, нарушающую целостность данных (например, недействительный идентификационный номер). Номера и описания ошибок хранятся в таблице Эти сведения можно запросить посредством оператора SELECT:

SELEC гог. description from niaster, dQo, sysmessages

Если процедура исполнена успешно, значение @@ERROR равно 0. При воз-

никновении ошибки функция @@ERROR возвращает номер ошибки, отличный от 0. Значение функции @@ERROR при выполнении каждого оператора так что это значение следует хранить в переменной или генерировать код возврата по завершении каждого оператора Следующий пример демонстрирует генерацию кода возврата при ошибки в базе данных:

ALTER PROCEDURE dbo. SalesForTitle

©Title varchar(80) = NULL ttE;ales int OUTPUT,

(SiTitleText varchar(80) OUTPUT

I №tle IS NULL .

RETURN(2) ELSE

IF (SELECT ШТ(*) FROM dbO. titles WHERE title LIK tle) = 0

RETURN(1) ELSE

SELECT WSales = ytd,.sales, ©TitleTexttitle

FROM dbo. titles WHERE title LIKI: ©Title , -

-- Обработка ошибок БД SQL Server.

IF TERROR о 0

RETURN(3)

Обратите внимание, что выполняет проверку значения @@ERROR после

исполнения оператора SELECT, который возвращает результирующий набор. Это паж-

3 процедур

ный момент, ведь задача функции ©©ERROR - проверка наличия ошибок базы данных после исполнения главной задачи хранимой

фрагмент кода исполняет процедуру и выводит стандартное сообщение об ошибке (если она есть):

DECLARE @y YtdSale3 int, TitleText varchar(80), @rCoae mt EXECUTE @r Code - SalesForTitle iaVtdSales dSales OUTPUT,

raTitleText leText OUTPUT itle = %Garlic%

Code = 0 SELECT Title = 0t TltleText,

Number of Sales dSales, Return code = i§ir Code ELSE

ode = 1

PRINNo matching titles in the database. Return e= + CONVERT{varchar(1). @r Code) ELSE

Code = 2

PRINT You must add a value fo e for this procedure to function properly. Return rade= + C0NVERTi;varchar(1 ),@r Code) ELSE

Code = 3

PRIN ere was a database error.

Создав хранимую процедуру и опробовав ее в реальной СУБД, вы узнаете, какие типы ошибок допускают пользователи во время ее исполнения. Эти данные вместе со знанием кодов позволят реализовать в процедурах дополнительные условия для

обработки ошибок.

Чтобы получить более подробную о результирующих и

использовании функции @@ERROR, выполните в SQL Server Books Online поиск по ключевым словам RESULT и ©©ERROR.

Вложенные процедуры

Под вложенностью хранимых понимают вызов одной хранимой процедуры из

другой. Одна хранимая может выполнять несколько задач, но создавать

более простые процедуры, которые для выполнения дополнительных задач вызывают другие хранимые дуры. Например, хранимая процедура ure A добавляет данные в существующую таблицу, а процедура ProcedureB отображает данные. После успешного добавления данных вызывает процедуру При этом

для просмотра табличных данных пользователь может запустить процедуру не запуская процедуру

Глубина вложенности процедур достигает 32 уровней. Однако числ едур. вызываемых одной процедурой, не ограничено. Процедура также способна рекурсивно вызывать сама себя. Для вызова одной процедуры из другой служит оператор EXECUTE. В упражнении 3 вы попробуете создать вложенные едуры в базе данных BookShopDB.