Create a Table-Valued Function in SQL Server

You can create a table-valued function (TVF) in SQL Server using the CREATE FUNCTION T-SQL syntax.

The syntax is slightly different depending on whether you’re creating an inline table-valued function (ITVF) or a multi-statement table-valued function (MSTVF).

Example 1 – Inline Table-Valued Function

Here’s an example of an inline table-valued function.

CREATE FUNCTION dbo.ufn_AlbumsByGenre_ITVF(@GenreId int)
RETURNS TABLE
AS
RETURN(  
    SELECT 
      ar.ArtistName,
      al.AlbumName,
      g.Genre
    FROM Genres g 
      INNER JOIN Albums al
        ON g.GenreId = al.GenreId 
      INNER JOIN Artists ar 
        ON al.ArtistId = ar.ArtistId
    WHERE g.GenreId = @GenreId
);
GO

We can tell that this is an inline table-valued function, because it doesn’t specify the structure of the return table. It simply states RETURNS TABLE, then relies on the SELECT statement to determine the return table’s structure.

In this case, the function requires the genre ID to be passed in as an argument.

See Create an Inline Table-Valued Function for examples of adding options such as schema binding and encryption.

Schema binding is usually a good idea, as it will prevent any adverse changes being done to the underlying objects that the function references.

Example 2 – Multi-Statement Table-Valued Function

Here’s how we’d write the function if we wanted it to be a multi-statement table-valued function.

CREATE FUNCTION dbo.ufn_AlbumsByGenre_MSTVF(@GenreId int)
RETURNS @Albums TABLE (
	ArtistName nvarchar(255),
	AlbumName nvarchar(255),
	Genre nvarchar(50)
	)
AS
BEGIN
    INSERT INTO @Albums
    SELECT 
        ar.ArtistName,
        al.AlbumName,
        g.Genre
    FROM Genres g 
        INNER JOIN Albums al
        ON g.GenreId = al.GenreId 
        INNER JOIN Artists ar 
        ON al.ArtistId = ar.ArtistId
    WHERE g.GenreId = @GenreId

    RETURN
END
GO

In this case, we use a variable of type table called @Albums and we explicitly specify the structure of the return table. The query results are stored in that variable, which is then returned when the function is invoked.

One of the benefits of MSTVFs is that they can contain multiple statements. Here it is again with an extra part added to the end.

CREATE FUNCTION dbo.ufn_AlbumsByGenre_MSTVF(@GenreId int)
RETURNS @Albums TABLE (
	ArtistName nvarchar(255),
	AlbumName nvarchar(255),
	Genre nvarchar(50)
	)
AS
BEGIN
    INSERT INTO @Albums
    SELECT 
        ar.ArtistName,
        al.AlbumName,
        g.Genre
    FROM Genres g 
        INNER JOIN Albums al
        ON g.GenreId = al.GenreId 
        INNER JOIN Artists ar 
        ON al.ArtistId = ar.ArtistId
    WHERE g.GenreId = @GenreId
 
    IF @@ROWCOUNT = 0
    BEGIN
        INSERT INTO @Albums
        VALUES (
            'None',
            'None',
            'None'
            )
    END

    RETURN
END
GO

See Create a Multi-Statement Table-Valued Function for examples of adding options such as schema binding and encryption.