• Статья

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

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

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

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

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

  • предложение RETURNS определяет имя локальной возвращаемой переменной для таблицы, которую возвращает эта функция. Предложение RETURNS также определяет формат таблицы. Область видимости имени локальной возвращаемой переменной является локальной в этой функции;

  • инструкции Transact-SQL в теле функции создают и вставляют строки в возвращаемую переменную, определенную предложением RETURNS;

  • при выполнении инструкции RETURN строки, вставленные в переменную, возвращаются в качестве выходных табличных данных функции. Инструкция RETURN не может иметь аргумента.

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

ПримечаниеПримечание

Параметр таблицы text in row автоматически устанавливается в 256 для таблицы, возвращаемой определяемой пользователем функцией. Этот параметр не может быть изменен. Инструкции READTEXT, WRITETEXT и UPDATETEXT не могут быть использованы для считывания или записи сегментов любых столбцов типа text, ntext или image в таблице. Дополнительные сведения см. в разделе Данные в строке.

Примеры

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

USE AdventureWorks2008R2;
GO
IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL
    DROP FUNCTION dbo.ufnGetContactInformation;
GO
CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)
RETURNS @retContactInformation TABLE 
(
    -- Columns returned by the function
    ContactID int PRIMARY KEY NOT NULL, 
    FirstName nvarchar(50) NULL, 
    LastName nvarchar(50) NULL, 
    JobTitle nvarchar(50) NULL, 
    ContactType nvarchar(50) NULL
)
AS 
-- Returns the first name, last name, job title, and contact type for the specified contact.
BEGIN
    DECLARE 
        @FirstName nvarchar(50), 
        @LastName nvarchar(50), 
        @JobTitle nvarchar(50), 
        @ContactType nvarchar(50);
    -- Get common contact information
    SELECT 
        @ContactID = BusinessEntityID, 
        @FirstName = FirstName, 
        @LastName = LastName
    FROM Person.Person 
    WHERE BusinessEntityID = @ContactID;
    -- Get contact job title
    SELECT @JobTitle = 
        CASE 
            -- Check for employee
            WHEN EXISTS(SELECT * FROM Person.Person AS p 
                        WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'EM') 
                THEN (SELECT JobTitle 
                      FROM HumanResources.Employee AS e
                      WHERE e.BusinessEntityID = @ContactID)
            -- Check for vendor
            WHEN EXISTS(SELECT * FROM Person.Person AS p 
                        WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'VC') 
                THEN (SELECT ct.Name 
                      FROM Person.ContactType AS ct 
                      INNER JOIN Person.BusinessEntityContact AS bec 
                          ON bec.ContactTypeID = ct.ContactTypeID  
                      WHERE bec.PersonID = @ContactID)

            -- Check for store
            WHEN EXISTS(SELECT * FROM Person.Person AS p 
                        WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'SC') 
                THEN (SELECT ct.Name 
                      FROM Person.ContactType AS ct 
                      INNER JOIN Person.BusinessEntityContact AS bec 
                          ON bec.ContactTypeID = ct.ContactTypeID  
                      WHERE bec.PersonID = @ContactID)
            ELSE NULL 
        END;
    -- Get contact type
    SET @ContactType = 
        CASE 
            -- Check for employee
            WHEN EXISTS(SELECT * FROM Person.Person AS p 
                        WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'EM') 
            THEN 'Employee'
            -- Check for vendor
            WHEN EXISTS(SELECT * FROM Person.Person AS p 
                        WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'VC')
            THEN 'Vendor Contact'
            -- Check for store
            WHEN EXISTS(SELECT * FROM Person.Person AS p 
                        WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'SC')
            THEN 'Store Contact'
            -- Check for individual consumer
            WHEN EXISTS(SELECT * FROM Person.Person AS p 
                        WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'IN') 
            THEN 'Consumer'
             -- Check for general contact
            WHEN EXISTS(SELECT * FROM Person.Person AS p 
                        WHERE p.BusinessEntityID = @ContactID AND p.PersonType = 'GC') 
            THEN 'General Contact'
        END;
    -- Return the information to the caller
    IF @ContactID IS NOT NULL 
    BEGIN
        INSERT @retContactInformation
        SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;
    END;
    RETURN;
END;
GO

Следующий пример использует вызов возвращающей табличное значение функции dbo.ufnGetContactInformation в предложении FROM инструкции SELECT.

USE AdventureWorks2008R2;
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(1209);
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(5);
GO