Проверим приведенную выше рекурсивную хранимую процедуру с помощью небольшого сценария:

DECLARE ©WorkingOut int DECLARE ©Workiugin int SELECT ©Workingin = 5

EXEC spFactorial ©Workingin, ©WorkingOut OUTPLT

PRINT CAST(©Workingin AS varchar) + factorial is + CAST(©WorkingOut AS varchar)

После вызова этого сценария на выполнение будет получен ожидаемый результат, равный 120:

5 factorial is 120

При попытке применить другие значения параметра ©Workingin обнаруживается, что хранимая процедура действует в полном соответствии с ожиданиями, если не считать двух довольно существенных описанных ниже возможных нарушений в работе.

Арифметическое переполнение, возникающее после того, как величина факториала становится слишком большой дяя данньгх типа int (или даже bigint).

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

Проверить, как происходит арифметическое переполнение, можно очень легко - достаточно задать для вычисления факториала любое большое число (в данном примере подходит любое число больше 13).

Для проверки того, что происходит при достижении предела рекурсии, равного 32 уровням, необходимо внести в хранимую процедуру небольшие изменения. В данном случае мы определим сумму ряда натуральных чисел от единицы до рассматриваемого числа (так называемое треугольное число). Применяемые при этом действия весьма напоминают вычисление факториала, за исключением того, что используется сложение, а не умножение. Поэтому сумма чисел от 1 до 5 составляет всего лишь 15, т.е. 5+4+3+2+1, и это означает, что переполнение не возникнет. Создадим новую хранимую процедуру для проверки данного алгоритма. Эта процедура почти полностью совпадает с хранимой процедурой вычисления факториала, за исключением нескольких небольших изменений:

CREATE PROC spTriangular ©ValueIn int, ©ValueOut int OUTPUT AS

DECLARE ©InWorking int DECLARE ©Outworking int IF ©ValueIn != 1 BEGIN

SELECT ©InWorking = ©Valuein - 1

EXEC spTriangular ©InWorking, ©Outworking OUTPUT SELECT ©ValueOut = ©Valuein + ©Outworking

ELSE

BEGIN

SELECT ©ValueOut = 1

RETURN GO

Вполне очевидно, что изменения весьма невелики. Аналогичным образом, для проверки этой хранимой процедуры достаточно внести небольшие изменения в оператор вызова хранимой процедуры и в текст оператора PRINT проверочного сценария:

DECLARE ©WorkingOut int DECLARE ©Workingin int SELECT ©Workingin = 5

EXEC spTriangular ©Workingin, ©WorkingOut OUTPUT

PRINT CAST (©Workingin AS varchar) + Triangular is + CAST (©WorkingOut AS varchar)

После вызова этого сценария на выполнение со значением ©Valuein, равным 5, будет получено ожидаемое значение 15:

5 Triangular is 15

Но попытка вызвать эту процедуру на выполнение со значением ©Valuein, большим 32, приводит к возникновению ошибки:

Msg 217, Level 16, State 1, Procedure spTriangular, Line 12

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)

К сожалению, какого-либо удобного способа преодоления этого ограничения не существует, поэтому вряд ли что-либо удастся сделать, если нет возможности каким-то образом сегментировать рекурсивные вызовы (осуществлять их прогон на глубину 32 уровня, затем полностью осуществлять выход из стека вызовов, после чего снова вызывать хранимую процедуру на выполнение для обработки очередной порции данных). Но следует учитывать, что большинство рекурсивных функций может быть преобразовано для использования стандартных конструкций организации цикла, а эти конструкции не налагают каких-либо пределов на количество итераций. Поэтому, прежде чем прибегнуть к использованию рекурсии, убедитесь в том, что стоящую перед вами задачу невозможно решить с помощью итерации.

Пользовательские функции

Пользовательские функции принадлежат к числу наиболее привлекательньгх объектов SQL Server. Возможность применения пользовательских функций (User Defined Function - UDF) появилась больше пяти лет тому назад, но до сих пор они остаются одними из самых недостаточно используемых и недооцененных объектов SQL Server. Эти объекты произвели потрясающее впечатление на специалистов по базам данньгх сразу после их внедрения корпорацией Microsoft в версии SQL Server 2000, но со времени появления инфраструктуры .NET пользовательские функции приобрели еще большие возможности.

Общее описание пользовательских функций

Пользовательские функции во многом напоминают хранимые процедуры и представляют упорядоченное множество операторов T-SQL, которые заранее оптимизированы, откомпилированы и могут быть вызваны для выполнения работы в виде единого модуля. Основное различие между пользовательскими функциями и хранимыми процедурами состоит в том, как в них осуществляется возврат полученных результатов. А в связи с тем, что для обеспечения предусмотренного в них способа возврата значений в пользовательских функциях должны осуществляться немного другие действия, к их синтаксической структуре предъявляются более жесткие требования по сравнению с хранимыми процедурами.

Для полноты изложения автор обязан подчеркнуть, что между уюльзовательскими функциями и хранимыми процедурами есть не только сходство, но и различие. Прежде всего, пользовательские функции, безусловно, не могут рассматриваться как замена д.гя хранимых процедур; они представляют собой всего лишь еще один способ организации кода, позволяющий получить дополнительные возможности.

Пользовательские функции подразделяются на два описанных ниже типа.

Возвращающие скалярное значение.

Возвращающие таблицу.

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

create function [<schema name>.]<function name>

( [ <@parameter name> [as] [<schema name>. ] <scalar data type> [ = <default value>] [ ,...n ] ] )

returns {<scalar type>table [(<Table Definition>)]} [ with [encryption][schemabinding]

[ returns null on null input called on null input ] [execute as { caller I self I owner I<user name>} ] ]

[as] { external name <external method> begin

[<function statements>]

{return <type as defined in returns clause>return (<select statement>)} end }[;]

Очевидно, что синтаксис оператора CREATE FUNCTION является довольно сложным, поскольку возможность применения необязательных частей этой синтаксической структуры зависит от того, какие компоненты были выбраны в других частях оператора создания пользовательской функции. При этом многое зависит от того, возвращает ли функция данные скалярного типа или таблицу, а также создается ли функция, основанная на использовании операторов языка T-SQL, или формируется функциональная структура, в которой применяются средства CLR и .NET. Рассмотрим отдельно пользовательские функции основного типа, применяемые в СУБД SQL Server (пользовательские функции, применяемые в инфраструктуре .NET, будут описаны более подробно в главе 14).

Пользовательские функции, возвращающие скалярное значение

По-видимому, пользовательские функции, возвращающие скалярное значение, больше всего напоминают функции, обычно применяемые в программном обеспечении. 1Сак и большинство собственных встроенных функций SQL Server (таких как GETDATE () или USER ()), пользовательские функции такого типа возвращают в вызывающий их сценарий или процедуру скалярное значение.

Важной отличительной особенностью пользовательских функций является то, что они в основном предназначены для получения самых разнообразных возвращаемых значений (а не исключительно результирующих наборов или выходных переменных); таким образом, при работе с ними можно не ограничиваться применением в качестве возвращаемых значений данных целочисленного типа, поэтому возвращаемые значения могут относиться к любому допустимому типу данных SQL Server (включая определяемые пользователем типы данных!), кроме данных типа BLOB, курсоров и вре-