Возвращающие табличное значение определяемые пользователем функции

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

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

CREATE FUNCTION function_name

( [ { @parameter_name [ AS ] parameter_data_type

[ = default ] }

[ ,...n ]

]

)

RETURNS @return_variable TABLE < table_type_definition >

[ WITH [ ENCRYPTION ] [ SCHEMABINDING ] [ EXECUTE AS Clause ]]

[ AS ]

BEGIN

function_body

RETURN

END

<table_type_definition>:: =

(

{ <column_definition> <column_constraint>

| <computed_column_definition> }

[ <table_constraint> ] [ ,...n ]

)

function_name

Имя пользовательской функции. Скобки после имени функции обязательны даже при отсутствии параметров

@parameter_name

Параметр пользовательской функции. Может быть объявлен один или несколько параметров.

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

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

Parameter_data_type

Тип данных параметра. Для параметров функций Transact-SQL допустимы любые типы данных за исключением типа данных timestamp, нескалярные типы cursor и table также не могут быть указаны в качестве типов данных параметров.

[ = default ]

Значение параметра по умолчанию. Если определено значение default, функция выполняется даже в том случае, если для данного параметра значение не указано.

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

<table_type_definition>

Определение табличного типа, включающее определение столбцов и ограничений целостности.

ENCRYPTION

Указывает, что компонент Database Engine преобразует исходный текст инструкции CREATE FUNCTION в скрытый формат. Пользователи, не имеющие доступа к системным таблицам или файлам баз данных, не смогут получить скрытый текст.

SCHEMABINDING

Указывает, что функция привязана к объектам базы данных, которые содержат ссылки на нее. Если аргумент SCHEMABINDING указан, нельзя изменить базовые объекты таким способом, который может повлиять на определение функции. Сначала нужно изменить или удалить само определение функции, чтобы удалить зависимости от объекта, который требуется изменить.

Привязка функции к объектам, на которые она ссылается, удаляется только в следующих случаях:

· При удалении функции.

· При изменении функции инструкцией ALTER, если не указан параметр SCHEMABINDING.

EXECUTE AS

Определяет контекст безопасности, в котором должна быть выполнена функция, т.е. какую учетную запись компонент Database Engine использует при проверке разрешений на объекты, на которые ссылается функция. Это повышает гибкость и безопасность управления разрешениями на цепочки владения между пользовательскими функциями и объектами, на которые они ссылаются.

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

Допустимые значения EXECUTE AS { CALLER | SELF | OWNER | 'user_name' }.

Function_body

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

Для функций, возвращающих табличное значение из нескольких инструкций, аргумент function_body представляет собой серию инструкций Transact-SQL, заполняющих возвращаемую переменную @return_variable.

TABLE

Указывает, что возвращаемым значением функции, возвращающей табличное значение, является таблица. Функциям, возвращающим табличное значение, могут передаваться только константы и локальные переменные.

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

Ни одна из инструкций Transact-SQL в возвращающей табличное значение функции не может возвращать результирующий набор непосредственно пользователю. Единственные данные, которые функция может вернуть пользователю, это таблица, состоящая из строк, вставленных в переменную TABLE, возврат происходит при выполнении инструкции RETURN.

Пример:

В следующем примере создается функция ufnGetUserInfo. В этой функции именем локальной возвращаемой переменной является @retUserInfo. Инструкции в теле функции вставляют строки в эту переменную для создания табличных результатов, возвращаемых этой функцией.

CREATE FUNCTION ufnGetUserInfo (@LoginName nvarchar(50))

RETURNS @retUserInfo TABLE

(

UserID int PRIMARY KEY NOT NULL,

FirstName nvarchar(50) NULL,

JobTitle nvarchar(2);

)

AS

BEGIN

DECLARE

@UserID nvarchar(50),

@FirstName nvarchar(50),

@JobTitle nvarchar(2);

SELECT

@UserID = UserID,

@FirstName = FirstName

FROM User

WHERE LoginName = @LoginName;

IF @UserID IS NOT NULL

BEGIN

SELECT @JobTitle =

CASE

-- Check for employee

WHEN EXISTS(SELECT * FROM Person.Person AS p

WHERE p.UserID = @UserID AND p.PersonType = 'EM')

THEN (SELECT JobTitle

FROM Employee AS e

WHERE e.UserID = @UserID)

-- Check for vendor

WHEN EXISTS(SELECT * FROM Person.Person AS p

WHERE p.UserID = @UserID AND p.PersonType = 'PT')

THEN (SELECT JobTitle

FROM Partner AS pt

WHERE pt.UserID = @UserID)

ELSE NULL

END;

END;

IF @UserID IS NOT NULL

BEGIN

INSERT @retUserInfo

SELECT @UserID, @FirstName, @JobTitle;

END;

RETURN;

END;

SELECT UserID, FirstName, JobTitle

FROM ufnGetUserInfo('admin');