Табличные функции SQL Server

Табличные функции — это пользовательская функция, доступная в SQL Server, которая возвращает таблицу. Существует два типа функций с табличным значением: встроенные функции с табличным значением и функции с табличным значением из нескольких операторов.

Встроенные табличные функции (ITVF)

ITVF используется для возврата наборов результатов одного оператора SELECT. Вы можете думать об этом как о параметризованном представлении.

-- Create inline table-valued functions
CREATE FUNCTION [dbo].[ufn_GetUsers](
    @filterFirstName nvarchar(200)
)
RETURNS TABLE
AS 
    RETURN (
        SELECT
            [FirstName],
            [LastName]
        FROM [dbo].[tblUser]
        WHERE [FirstName] LIKE @filterFirstName
    );
-- Executing inline table-valued functions
SELECT * FROM [dbo].[ufn_GetUsers]('%A%') ORDER BY [FirstName];

Кроме того, также вполне допустимо использовать Common Table Expression (CTE) в ITVF.

CREATE FUNCTION [dbo].[ufn_GetUsers](
    @filterFirstName nvarchar(200)
)
RETURNS TABLE
AS 
    RETURN (
        WITH CTE_Results AS (
            SELECT
                [FirstName],
                [LastName]
            FROM CTE_Results
            WHERE [dbo].[tblUser]
        )
        SELECT
           *
        FROM CTE_Results
        WHERE [FirstName] LIKE @filterFirstName
    );

Обратите внимание, что ITVF не может иметь блоки BEGIN и END, инкапсулирующие инструкцию RETURN. Вы получите сообщение об ошибке «Неверный синтаксис рядом с BEGIN» при использовании инструкции BEGIN и END следующим образом.

-- Create inline table-valued functions
CREATE FUNCTION [dbo].[ufn_GetUsers](
    @filterFirstName nvarchar(200)
)
RETURNS TABLE
AS
BEGIN
    RETURN (
        SELECT
            [FirstName],
            [LastName]
        FROM [dbo].[tblUser]
        WHERE [FirstName] LIKE @filterFirstName
    );
END

Табличные функции с несколькими операторами (MSTVF)

MSTVF всегда используется для возврата наборов результатов, но может содержать несколько операторов SQL внутри блоков BEGIN и END, в отличие от ITVF, который может содержать только один оператор SQL. Необходимо создать табличную переменную и вставить в нее данные. Он используется, когда нам нужно выполнить дополнительные манипуляции с данными (например: INSERT, UPDATE, DELETE) в данных SELECT перед вставкой в ​​окончательный вывод.

-- Create multi statement table-valued functions
CREATE FUNCTION [dbo].[ufn_GetUsers](
    @filterFirstName nvarchar(200)
)
RETURNS @Users TABLE (
    [FirstName] nvarchar(200),
    [LastName] nvarchar(200),
    [Role] varchar(50)
) AS
BEGIN
    INSERT INTO @Users
    SELECT [FirstName], [LastName], 'student'
    FROM [dbo].[tblStudent]
    WHERE [FirstName] LIKE @filterFirstName;
    INSERT INTO @Users
    SELECT [FirstName], [LastName], 'teacher'
    FROM [dbo].[tblTeacher]
    WHERE [FirstName] LIKE @filterFirstName;
    RETURN;
END

Как правило, рекомендуется избегать использования MSTVF, поскольку он плохо влияет на производительность. Поэтому, как правило, всегда используйте ITVF вместо MSTVF, когда это возможно.

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

CREATE FUNCTION [dbo].[ufn_GetUsers](
    @filterFirstName nvarchar(200)
)
RETURNS TABLE
AS 
    RETURN (
        SELECT [FirstName], [LastName], 'student'
        FROM [dbo].[tblStudent]
        WHERE [FirstName] LIKE @filterFirstName
        UNION ALL
        SELECT [FirstName], [LastName], 'teacher'
        FROM [dbo].[tblTeacher]
        WHERE [FirstName] LIKE @filterFirstName
    );

Дополнительные чтения:

Табличные функции SQL Server

В чем разница между встроенными табличными значениями и многооператорными функциями в SQL?