Настройки окружения

If i BJECT !ijJDt :.-siRfeiflor.c LnpCvuirS , IS NC fiU. L. CROP PROC dbo.L-siRegiOPalEnpioyees

Si ANSI N:.LES OFT GO

CREATE CROC dbo.MstRegiorditTpicy-rfS tOv CT TvircEdrfjG AS

SELECT EmployeeiD, LasLName, Fir-stNane, Reckon pRGH ercoyees

WHERE Realcn=3region

SET AI,SI MjLLS on GO

EXEC 1 sireg-cnalemployees NULL

Если бы пе SET ANSI NULLS, процедура пе смогла бы успешно сравнить пара.метр (©region, значение которого равтю NULL, со столбцом Region в таблице Employees базы данных Nortlnwind. Запрос никогда не вернул бы 1Ш одной записи, поскольку в соответствии со спецификацией ANSI одно NULL-значение никогда не равно друго.му. Удобство этого .метода становится особенно заметным, когда процедура и.меет .унюжество иара.метров, которые могут прини.мать значение NULL, как ©region. Если бы не было воз\к)жностп сравнить значения NULL между собой, так же как и He-NULL-значения, каждый параметр, который мог бы пртиш-мать значение NULL, потребовал бы дополнительной обработки, увеличивая те.м са.\ил.м объе.м кода запроса.

Поскольку SQL Server храпит значения установок QUOTEDJDENTIFIER и ANSI NULLS вместе с каждой хранимой процедурой, можно быть уверенны.м, что они будут иметь необходимые значения, когда хранимая процедура будет запущена. Сервер устанавливает значения этих параметров такими, какими oini были при создании хранимой процедуры, и восстанавливает значения после завершения выполнения. Так что, если у нас есть такой код:

SET ANSI ,NULLS ON

EXEC listnegionalempioyees NULL

храни.мгш процедура псе равно выполняется так, как если бы ANSI NULLS была установлена в OFF. Значения 1Шстроек QUOTEDJDENTIFIER и ANSI NULLS храни.мой ироцедуры .угожно получить с по.мощью функции OBJECTPROPERTY(). Вот при.мер:

USE pubs

SELECT OBJECTPROPERTY(OBJECT ID(table).ExeclsOuotedldentOn) AS Quotedldenf USE Northwind

SELECT OBJECTPROPERTY(OBJECT ID(listregionalemployees).ExecIsAnsiNullsOn) AS AnsiNulls

Quotedldent

О

Существует множество других команд, которые влияют иа то, как будет выполняться код Transact-SQL - и внутри и вне храгнтмых процедур. Такие команды, как SET TEXTSIZE, SET CURSOR CLOSE ON COMMIT, SET IMPLICIT.TRANSACTIONS и многие другие, определяют, как поведет себя хранимая пронедура в ходе выполнения. Если вам необходн.мо, чтобы хранимая процедура имела определенное значение соответствующей настройки, определите его в процедуре как можно раньше и опишите в ко.мментариях, зачем вам это понадобилось.

Параметры

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

EXEC sp msforeachtable (acoiiimandI-sp help ? , iSreplacechar = ?

EXEC sp msforeachtable sp help ? . ?

Преимущество ссылки на параметры по имени заключается в том, что при этом параметры можно указывать в произвольном порядке. Параметр, для которого указано значение по умолчанию, ,мож1ю опустить, либо указать вместо него ключевое слово DEFAULT, как .здесь:

EXEC sp msforeachtable (acommandl=sp help ? . ?replacechar = DEFAULT

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

EXEC sp who (aioginame=NULL ,

(результаты сокращены)

spid status loginame

1 sleeping sa

2 background sa

3 background sa

6 background sa

7 sleeping CALIGULA\KHEN

8 sleeping CALIGULA\KHEN

9 sleeping CALIGULA\KHEN

В этом случае процедура sp who возвращает список всех активных соединений, поскольку параметр @loginame равен NULL. Если процедуре sp who передать имя учётной записи, она вернет только те соединения, которые были установлены с использованием этой учетной записи. Мы увидели бы тот же результат, если бы вообще опустили параметр @loginame - были бы выведены все соединения. Я указал NULL только для примера.

AnsiNulls

Выходные параметры

Выходные параметры позволяют возвращать значения из хранимых пронедур. Этими иараметра\И1 мотут быть целые числа, строки, даты и даже курсорьь Вот при-мер:

USE puts

F CBJECi -;aco.hstsaies) IS \Ci NELL DEC- PRCC dbo.iistsales

CREATE PRCC abc. i istsales @bestse-le- tid CUT, Otopsales int CUT. Psa lescurson cjnso varying CUT

SELECT CDestsei ierbestse ler, (ptopsalestotalsales FRCM <

SELECI TCP : title -d AS bestseller, SUM(qty) AS tctalsales

FROM sales

GRCuP BY t;ile id

ORDER B- 2 desc) bestsellers DECLARE S CURSOR LOCAL

FOR SElEC: * FROM sales OPEN s

SET (asalescurscr-s return;0)

LECLARE iptopsales int, bestseller tid, Psalescursor cursor tXEC listsales Ocestselier OUl, gtopsales OUT, (Psalescursor OUT SELECT gbestseler, iptopsales FEICH @salescursor ClOSE Osalescu-so-DEALIOCATE iasalescursor

PS2091 191

stor ;a ord nun: orG date qty payterms t:t;e id

6380 6871 1994-09-14 00:00:00,000 5 Net 60 BUi032

Выходные пара\гетры обозначаются ключевым словом OUTPUT (его .чюжно сократить - OUT). Учтите, что ключевое слово OUT необходи.мо указывать в списке иара.метров и при запуске процедуры с пoющью ко.манды EXEC, и при ее создании. Н процедура, и тот, кто ее вызывает, должны указывать, какие пара-.метрьт являются выходными.

Иснользова1И1е выходного пара.метра типа cursor - это хоропшя ачьтерпа-тива возврату данных вызывающей стороне. Обратите вии.мание на при.менение ключевого слова VARYING при определенти! курсорного пара.метра. Это ключевое слово необходн.\[о при задепствованип курсорных иара.метров и означает, что возвращаемое значение нескаляриое - то есть возврап1ает больше одного значения. Курсорные параметры могут быть только выходными, так что ключевое слово OUT также иеобходи.мо.

Коды завершения

Процедуры могут возвращать код завершения, используя.команду RETURN. Код завершения О означает успех, значения от -1 до -14 указывают на различные ошибки, значения от -15 до -99 зарезервированы для будущего использования. В табл. 15.1 приведены значения кодов завершения от -1 до -14:

Таблица 15.1. Коды завершения и их описание

Код Описание

-1 Object missing (Объект отсутствует)

-2 Data type error occurred (Произошла ошибка типа данных)

-3 Process chosen as deadlock victim (Процесс был выбран в качестве жертвы блокировки)

-4 Permission error (Ошибка, связанная с правами доступа)

-5 Syntax error (Синтаксическая ошибка)

-6 Miscellaneous user error (Прочая пользовательская ошибка)

-7 Resource error (Ошибка ресурсов)

-8 Nonfatal internal error (Нефатальная внутренняя ошибка)

-9 System limit reached (Достигнуто системное ограничение)

-10 Fatal internal inconsistency error (Фатальная внутренняя ошибка целостности)

-11 Fatal internal inconsistency error (Фатальная внутренняя ошибка целостности)

-12 Corrupt table or index (Таблица или индекс повреждены)

-13 Corrupt database (База данных повреждена)

-14 Hardware error (.Аппаратная ошибка)

Получить код завершения хранимо11 процедуры можно, сохранив его в переменную типа integer, например так:

DECLARE lares int

EXEC (ares=sp who *

Получение параметров процедур

Получить параметры процедуры (включая ее код завери1ения - параметр 0) можно с по.мощью недокументированной процедуры sp procedure params rowset. Вот пример:

EXEC sp procedure params rowset sp MSforeachtab1e (результаты сокращены)

PROCEDURE CATALOG PROCEDURE SCHEMA PROCEDURE NAME PARAMETER NAME ORDINAL POSITION PARAMETER TYPE

master dbo sp MSforeachtable; 1 RETURNJALUE 0 4

master dbo sp MSforeachtable;l lacommandl 1 1

master dbo sp MSforeachtable:l Oreplacechar 2 1

master dbo sp MSforeachtable:l iacommand2 3 1

Лучше это все же делать с помощью представле1шя INFORMATION SCHEMA. PARAMETERS. - Примеч. перев.

Языковые конструкции управления ходом выполнения 343

mste; doc S3 i:oreacr:.jDi-j: I fwnereand 5 1

raster dbo sp .S< oreacn; aoU-: 1 JpreccOTnc 6 !

iPdster dbo s? foreacniaD!e;! stcaxs-;!! 7 !

Основные замечания относительно параметров

о Определяйте значения параметров ио \-молчанП1(), где это возможно. Значение по у.молчаиию может бьтть константой или NULL.

о Проверяйте корректность параметров .xpann.Moii процедуры в са.мо.м начале.

О Пара.метры проще передавать, если они и.меют интуитивно попятные и.мена.

О Так как названия параметров являются лока;пл(ьь\и1, пара.метры с одинако-вьь\н1 названия.\н1 .можно исиользовать в различных ироцедурах.

О Информация о параметрах процедуры храи1тгся в ciiCTe.vnioii таблице syscolumns (вот откуда берет информашио sp procedure params rowset).

О Храии.мая процедура .может и.меть до 1024 пара.метров. Кол1И1ество и обьем иа.мяти, заии.мае.мый локальны.ми пере.ме1!ны.ми хранимой процедуры, ограничено только количество.м иа.мяти, досту1И1ы.м SQL Server.

Важные автоматические переменные

Авто.матические переменные (те, что в Book.s Onhne назьнзаются функниями ) обычно используются внутри храни.мых п1)оцедур. Те.м самым они в иекоторо.м роде связаны с храии.\ия.\И1 процедура.\ик Однако некоторые из них более тесно связаны с прп.\[еиенпе.м храни.мых процедур, че.м другие. Инфopщция о них приведе}1а в табл. 15.2.

Таблица 15.2. Автоматические переменные, связанные с хранимыми процедурами

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

Обсуждение храни.мых процедур пе было бы полным без опнсанпя операторов управления ходом выполнения. Отдельные ко.манды Transact-SQL определяют порядок выполнения команд храии.мой процедуры или пакета ко.мащ. Эти команды называются команда.ми управления ходом выполнения - IF... ELSE, WHILE, GOTO, RETURN, WAITFOR, BREAK, CONTINUE и BEGIN... END. He буде.\[ новто-

рять то, что уже довольно хорошо описано в Books Online, просто приведем простую хранимую процедуру, которая иллюстрирует использование всех этих команд: USE pubs

IF OBJECT IDCdbo.listsales) IS NOT NULL DROP PROC dbo.listsales

CREATE PROC dbo.r.stsales Ptitlejd tid=NULL AS

IF (iatnie id=7?) GOTO Help - Простой IF -- IF с блоком BEGIN..END

IF NOT EXISTSCSELECT * FROM titles WHERE title id=iatitleJd) BEGIN PRINT Invalid title id

WAITFOR DELAY 00:00:0.3 -- Задержка 3 с. чтобы посмотреть

сообщение

RETURN -1

IF NOT EXISTSCSELECT * FROM sales WHERE title id=(atitle id) BEGIN PRINT NO sales for this title

WAITFOR DELAY 00:00:03 -- Задержка 3 с. чтобы посмотреть

сообщение

RETURN -2

DECLARE (aqty int. (Btotalsales int SET iatotalsales=0

DECLARE с CURSOR

FOR SELECT qty FROM sales WHERE t itle id=iatitle id OPEN с

FETCH с INTO laqty

WHILE (@(aFETCH STATUS=0) BEGIN - Цикл WHILE IF ((aqty<0) BEGIN

Print Bad qftantity encountered *

BREAK -- Выйти из цикла немедленно END ELSE IF (gqty IS NULL) BEGIN

Print NULL quantity encountered - пропускаем FETCH с INTO gqty

CONTINUE -- Продолжить со следующей итерации цикла

SET (atotalsales-iatotalsales+gqty FETCH с INTO (aqty

CLOSE с DEALLOCATE с

SELECT latitlejd AS TitlelD. (atotalsales AS TotalSales

RETURN 0 -- Возвращаемся из процедуры с индикацией успешного выполнения

Help:

EXEC sp usage iaobjectname=listsales.

№sc-Lists the total sales for a title, @parameters=@titlejd= 10 of the title you want to check . (aexample=EXEC listsales Р5209Г .

Ошибки 345

iPaJiPor Ken henaeiscii, geiiiai = kherii?khen,coni, {3versicn=Г . grevis-onC ,

(Pdatec-eated19990803. ?aat.el55:ct-anged 1-990818 WAIiFCR DELAl C0:0Q;Q3 Зздео-Кй 3 с, :cC= пос-О'реь СОа,лы.(: RE 1 URN -1

EXEC 1 isibaiei [-32091 EXEC iistiaes bedone EXEC listsales PC9999

TitlelD TotalSales

PS209] 191

Invalid t-.tle id

No sales Tor ths title

Хранимые процедуры посылают cooouieinw об опаибках клиентским п1)иложе-ниям, используя команду RAISEERROR. RAISERROR не влияет на ход выполнения хранимой ироцедуры, а просто выводит сообщение, устанавливает соответствующее значение переменной @ERROR и также лгожет записать сообщение в журналы SQL Server и онерационной системы. RAISERROR южeт ссылаться на сообщения об ощибках из таблицы sysmessages (в том числе и иа добавленные туда с помощью процедуры sp addmessage), или вы люжете указать свое сообщение об ощибке. Если вы передаете в RAISERROR свое сообщение, но.\гер оипгбки устанавливается равным 50 ООО. В колганде RAISERROR .можно задать формат сообщения подобно то.му, как в функции PRINTF() языка С, что позволяет вам задать аргументы для возвращаемых сообщений.

В ко.\гаиде RAISERROR можно указать значение и для уровня (severity), и для статуса (state) ошибки. Значения уровня ошибки, .меньшие 16, генерируют сообщения в журнале приложения (если включена запись в журнал). Значение уровня опиюки 16 генерирует предупреждающее сообщение в журнале событии, а в случае, если зиачеиия больше 16, в журнале событиГ] регистрируется ошибка. Сообщения со значениями уровня 0!пибки до 18 лгогут быть ргнициированы любы.\пг пользователями; сообщения со зпаче1И1ями уровня оии1бки от 19 до 25 MorjT быть инициированы только члена.\ги группы sysadmin, при это.м обязательно указывать опцию WITH LOG. Значения уровня ошибки с 20 и выше считаются фататьиы.ми и вызывают р;1зрыв клиентского соединения. Статус ошибки не и.меет какого-либо специального предназначения и может быть применен для возврата инфор.мации об ошибке клиентско.му приложению. Если вызвать ошибку со значением статуса, равным 127, утилиты ISQL и OSQL установят значение системной переменной ERRORLEVEL равное но.меру опитбки, вызванной операторо.м RAISERROR. Учтите, что в отличие от Bepcnii до 7.0 утилита ISQL не завершается немедленно, если указан статус 127 - она просто устанавливает ERRORLEVEL; в отличие от нее OSQL немедленно завершается. Так что если у иас есть такой пакет SQL:

RAIStRRORC-Setting the OS ERRORLLVEL variable,16.127) WITH NOWAIT PRINT Prior to 7.0. execution, vjould never make it here m ISOL

и мы запустим его из ко.мандного пакета опсрацио1И10й системы:

(РЕСНО OFF

isql -Usa -Р -iraiserrorOl.sql ECHO ERRORLtVEL osql -Usa -P -iraiserrorOl.sql ECHO XERRORLEVEL*

ВОТ что получится:

D;\>RAISERROR

.1> 2> 3> Msg 50000, Level 16. State 127, Server CAi IGULA, Line I

Setting the OS ERRORLEVEL variable

Prior to 7,0, execution vjojIc revo make it here in ISOL

50000

I> 2> 3> Msg 50000, Level 16, State 12/. Server CALIGULA. Proceoure . Line I [Microsoft][ODBC SQL Server DnverJLSQL ServerJSetting the OS ERRORLEVEL variable 50000ЦУ

Это удобно для немедленного выхода из ко.мандного пакета без генерации ненужных предупреждений и записей в систе.мно.м журнале событий. Хотя, чтобы разорвать соединение, вы .можете вызвать ошибку с высоким уровнем, это создаст запись в журнале и теоретически вызовет ненужное внимание к вообще-то рядовому событию: прерыванию пакета до его полного завершения. И хотя вы можете прервать пакет с помощью команды EXIT, ие1)еменная ERRORLEVEL не будет установлена, так что вы не сможете узнать, почему пакет завершился.

RAISERROR имеет множество опций, влияюпи1х па ее поведение. Если указать пара.метр WITH LOG, информация об оншбкс будет записана в журнал событий Windows (в случае, если SQL Server работает иод управлением onepaiuionnoii системы Windows NT) и в журнал ошибок SQL Server независимо от того, был ли указан параметр WITH LOG при создании сообще!Н1я с помопн>ю sp addmessage. Если указать параметр WITH NOWAIT, сообнютге будет немедленно возвраищно клиенту. Опция WITH SETERROR заставляет @@ERROR вернуть но.мер последней произошедншй ошибки, независимо от уровня сообщения о ней.

Системная процедура sp addmessage добавляет в таблицу sysmessages пользовательские сообщения, которые могут быть в дальнейшем инициированы (возвращены клиенту) с помощью ко.манды RAISERROR. Пользовательские сообнюния должны и.меть номера ошибок начиная с 50000. В основном системные сообщения SQL Server применяются при разработке программ с многоязыковым ин-терфейсо.м. Поскольку при добавлении сообн1ения с помощью sp addmessage указывается идентификатор языка, можно добавить отдельную версию сообщений об ошибках для каждого языка, поддерживаемого вашим приложением. Тогда, если храни.мая процедура ссылается на сообщение по номеру, сообщение будет возврап1ено в ириложение в соответствии с текущими языковыми 1ш-стройками SQL Server.

Так как RAISERROR .может одновременно отображать сообщение, устанавливать значение переменно!! @@ERROR и В!>!Водить сообщс!!ие, И!!огда она !![)име-1!яется !ie для !н.1вола сообщс!!ий об 0!!!ибках. Оче!1ь удобно нс!!Ользовать ее

ошибки 347

способность (fjopMciTHpoBaTb строки, отличные от сообщений об ошибках, нолоб-ло функции printf(). Вот нри.мер, в которо.м RAISERROR применяется для и1)о-смотра таблицы:

DECLARF с CURSOR

FOR SELEC t1Ce ic. SUM(qty) as sales FROM sales GROUP BY t1tle id DECLARE @title ic tid. (aqty rt OPEN с

RAISERRCRCStarting loop.l.i) -- Инициализируем (PC=ERROR WHILE ((3(aERR0R<=l) BEGIN

FETCH с INTO (atitle id. Oqty IF ((apFETCHJTATUS-O)

RAISERROR(Title lO%s has sold %6 units.1.1,gtitle id.Coqty) ELSE BREA,<

CLOSE с DEALLOCATE С

Msg 50C00. Level 1. State 5GCCC Starting loop

Msg 50000. Level 1. State 50000 Title ID BU103? has sold 45 units Msg 50000, Level 1. State 50000 Title ID BUllll has sold 25 units Msg 50000. Level 1. State 50000 Title ID BU2075 has sold 35 un4s Msg 50000. Level 1. State 50000 Title ID BU7832 has sold 15 units Msg 50000, Level 1, State 50000 Title ID MG2222 has sold 10 units Msg 50000, Level 1, State 50000 Title ID MC3021 has sold 40 units Msg 50000, Level 1, State 50000 Title ID MC3026 has sold 30 unUs Msg 50000, Level 1, State 50000 Title ID PC1035 has sold 30 units Msg 50000, Level 1, State 50000 Title ID PCBBBB has sold 50 units Msg 50000, Level 1, State 50000 Title ID PS1372 has sold 20 units Msg 50000, Level 1, State 50000 Title ID PS2091 has sold 191 units Msg 50000, Level 1, State 50000 Title ID PS2106 has sold 25 units Msg 50000, Level 1, State 50000 Title ID PS3333 has sold 15 units Msg 50000, Level 1, State 50000 Title ID PS7777 has sold 25 units Msg 50000, Level 1, State 50000 Title ID TC32i8 has sold 40 units Msg 50000, Level 1, State 50000 Title ID TC4203 has sold 20 units

Msg 50000. Level 1. State 50000 Title ID TC7777 has sold 20 units

Конечно, строки Msg... не.много раздражают, но вы можете удалить их в своем клиентском приложении, если решите использовать этот подход.

@@ERROR

Возьмите за правило проверять значение @@ERROR в своих хранимых процедурах после выполнения важных участков кода, особенно после операций, связанных с модификацией данных. Показатель хорошего кода - последовательная проверка ошибок. Поскольку Transact-SQL пока не поддерживает структурированную обработку исключений, проверка @@ERROR зачастую лучший способ избежать непредвиденных обстоятельств.

xp logevent

Вы можете задействовать системную процедуру xpjogevent, чтобы добавить сообщение в журнал ошибок SQL Server или в журнал событий Windov/s NT. Ее основное отличие от RAISERROR состоит в том, что клиенту не посылается сообщение об ошибке. Номер сообщения или строка, переданная xpjogevent, регистрируются без уведомления клиента.

Вложенные вызовы

Максимальный уровень вложенности храни.мых процедур - 32. Текущий уровень вложенности можно получить с помощью автоматической переменной @@NESTLEVEL. Для уровня пакета команд она возвращает 0; 1 - для каждой хранимой процедуры, вызванной с уровня О (и из триггеров первого уровня); 2 - для каждой процедуры, вызванной с уровня 1, и так далее. Объекты (включая временные таблицы) и курсоры, созданные в хранимой процедуре, видимы всем объектам, которые вызываются из нее. Объекты и курсоры, созданные на уровне О, видимы всем объектам.

Рекурсия

Transact-SQL поддерживает рекурсию. Рекурсия - это способ решения задачи с помощью разбиения ее на более мелкиеи с применением к ним того же алгоритма. Чаще всего рекурсия применяется при создании синтаксических анализаторов и при решении различных численных задач, которые вызываю сами себя для повторного вычисления с использованием такой же логики. Вот пример, в котором создается хранимая процедура, вычисляющая факториал числа:

SET NOCOUNT ON

USE master

IF 0BJECT ID(dbo.sp calcfactor1al) IS NOT NULL DROP PROC dbo.sp calcfactorial

DECLARE gtypestr varchar(20)