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.