The SELECT statement is probably the most commonly used statement in SQL Server. Most of the time this statement is run against a view or directly against a table to retrieve rows of tabular data.

But views and tables aren’t the only objects you can run a SELECT statement on. The SELECT statement can also be used on other objects such as rowset functions, OPENXML, and user-defined functions.

This article provides an example of selecting data via a table-valued function.

Example 1 – Basic Function

Here’s a quick function that selects basic data from a table via an inline table-valued function.

SELECT * FROM udf_Cats_ITVF();

Result:

+---------+-----------+------------+
| CatId   | CatName   | Phone      |
|---------+-----------+------------|
| 1       | Garfield  | 9871237654 |
| 2       | Felix     | 8871237651 |
| 3       | Tom       | 7871237652 |
| 4       | Fetch     | 6871237653 |
+---------+-----------+------------+

The function looks like this:

CREATE FUNCTION dbo.udf_Cats_ITVF()
    RETURNS TABLE
AS
RETURN (
    SELECT 
        CatId,
        CatName,
        Phone
    FROM dbo.Cats
    );
GO

This function simply selects all rows from a table. No arguments are required.

If you wanted to select a certain cat, you’d need to add a WHERE clause.

SELECT * FROM udf_Cats_ITVF()
WHERE CatName = 'Fetch';

Result:

+---------+-----------+------------+
| CatId   | CatName   | Phone      |
|---------+-----------+------------|
| 4       | Fetch     | 6871237653 |
+---------+-----------+------------+

Now I have to admit, this function is kind of superfluous, because we could have just selected the data directly from the table. Or we could have created a view to do the job. But user-defined functions have a benefit that tables and views don’t have: parameters.

Example 2 – Function with Parameters

One of the benefits of table-valued functions is that they support parameters. This is where the function becomes a bit more useful. Some refer to table-valued functions as “parameterized views”, because they behave just like a view but with the added functionality of allowing parameters.

So we could create a variation of the previous function to accept an argument for the cat’s name.

SELECT * FROM udf_CatsByName_ITVF('Fetch');

Result:

+---------+-----------+------------+
| CatId   | CatName   | Phone      |
|---------+-----------+------------|
| 4       | Fetch     | 6871237653 |
+---------+-----------+------------+

The new function looks like this:

CREATE FUNCTION dbo.udf_CatsByName_ITVF( @CatName varchar(70) )
    RETURNS TABLE
AS
RETURN (
    SELECT 
        CatId,
        CatName,
        Phone
    FROM dbo.Cats
    WHERE CatName = @CatName
    );

GO

Example 3 – Joins

Table-valued functions can be included in joins.

Here, I select all columns from a table-valued function that returns all albums by a given artist:

SELECT * FROM ufn_AlbumsByArtist(1);

Result:

+------------+-------------------------+---------+
| ArtistId   | AlbumName               | Genre   |
|------------+-------------------------+---------|
| 1          | Powerslave              | Rock    |
| 1          | Somewhere in Time       | Rock    |
| 1          | Piece of Mind           | Rock    |
| 1          | Killers                 | Rock    |
| 1          | No Prayer for the Dying | Rock    |
+------------+-------------------------+---------+

The only problem with this function is that it doesn’t return the artist name. If I want the artist name, I need to join it with the table that contains that data. In this case the table that contains the artist name is called Artists, so I can modify my query as follows:

SELECT  
    ar.ArtistName,
    aba.AlbumName,
    aba.Genre
FROM ufn_AlbumsByArtist(1) aba
INNER JOIN Artists ar
ON aba.ArtistId = ar.ArtistId;

Result:

+--------------+-------------------------+---------+
| ArtistName   | AlbumName               | Genre   |
|--------------+-------------------------+---------|
| Iron Maiden  | Powerslave              | Rock    |
| Iron Maiden  | Somewhere in Time       | Rock    |
| Iron Maiden  | Piece of Mind           | Rock    |
| Iron Maiden  | Killers                 | Rock    |
| Iron Maiden  | No Prayer for the Dying | Rock    |
+--------------+-------------------------+---------+