{"id":7785,"date":"2020-02-06T02:38:18","date_gmt":"2020-02-06T09:38:18","guid":{"rendered":"https:\/\/database.guide\/?p=7785"},"modified":"2020-02-09T15:40:00","modified_gmt":"2020-02-09T22:40:00","slug":"select-data-via-a-table-valued-function-in-sql-server","status":"publish","type":"post","link":"https:\/\/database.guide\/select-data-via-a-table-valued-function-in-sql-server\/","title":{"rendered":"Select Data via a Table-Valued Function in SQL Server"},"content":{"rendered":"

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

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

This article provides an example of selecting data via a table-valued function<\/a>.<\/p>\n

<\/p>\n

Example 1 – Basic Function<\/h2>\n

Here’s a quick function that selects basic data from a table via an inline table-valued function.<\/p>\n

\r\nSELECT * FROM udf_Cats_ITVF();\r\n<\/pre>\n
Result:<\/p>\n
\r\n+---------+-----------+------------+\r\n| CatId   | CatName   | Phone      |\r\n|---------+-----------+------------|\r\n| 1       | Garfield  | 9871237654 |\r\n| 2       | Felix     | 8871237651 |\r\n| 3       | Tom       | 7871237652 |\r\n| 4       | Fetch     | 6871237653 |\r\n+---------+-----------+------------+\r\n<\/pre>\n
The function looks like this:<\/p>\n
\r\nCREATE FUNCTION dbo.udf_Cats_ITVF()\r\n    RETURNS TABLE\r\nAS\r\nRETURN (\r\n    SELECT \r\n        CatId,\r\n        CatName,\r\n        Phone\r\n    FROM dbo.Cats\r\n    );\r\nGO\r\n<\/pre>\n
This function simply selects all rows from a table. No arguments are required. <\/p>\n
If you wanted to select a certain cat, you’d need to add a WHERE<\/code> clause.<\/p>\n
\r\nSELECT * FROM udf_Cats_ITVF()\r\nWHERE CatName = 'Fetch';\r\n<\/pre>\n
Result:<\/p>\n
\r\n+---------+-----------+------------+\r\n| CatId   | CatName   | Phone      |\r\n|---------+-----------+------------|\r\n| 4       | Fetch     | 6871237653 |\r\n+---------+-----------+------------+\r\n<\/pre>\n
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. <\/p>\n
Example 2 – Function with Parameters<\/h2>\n
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.<\/p>\n
So we could create a variation of the previous function to accept an argument for the cat’s name. <\/p>\n
\r\nSELECT * FROM udf_CatsByName_ITVF('Fetch');\r\n<\/pre>\n
Result:<\/p>\n
\r\n+---------+-----------+------------+\r\n| CatId   | CatName   | Phone      |\r\n|---------+-----------+------------|\r\n| 4       | Fetch     | 6871237653 |\r\n+---------+-----------+------------+\r\n<\/pre>\n
The new function looks like this:<\/p>\n
\r\nCREATE FUNCTION dbo.udf_CatsByName_ITVF( @CatName varchar(70) )\r\n    RETURNS TABLE\r\nAS\r\nRETURN (\r\n    SELECT \r\n        CatId,\r\n        CatName,\r\n        Phone\r\n    FROM dbo.Cats\r\n    WHERE CatName = @CatName\r\n    );\r\n\r\nGO\r\n<\/pre>\n
Example 3 – Joins<\/h2>\n
Table-valued functions can be included in joins. <\/p>\n
Here, I select all columns from a table-valued function that returns all albums by a given artist:<\/p>\n
\r\nSELECT * FROM ufn_AlbumsByArtist(1);\r\n<\/pre>\n
Result:<\/p>\n
\r\n+------------+-------------------------+---------+\r\n| ArtistId   | AlbumName               | Genre   |\r\n|------------+-------------------------+---------|\r\n| 1          | Powerslave              | Rock    |\r\n| 1          | Somewhere in Time       | Rock    |\r\n| 1          | Piece of Mind           | Rock    |\r\n| 1          | Killers                 | Rock    |\r\n| 1          | No Prayer for the Dying | Rock    |\r\n+------------+-------------------------+---------+\r\n<\/pre>\n
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<\/code>, so I can modify my query as follows:<\/p>\n
\r\nSELECT  \r\n    ar.ArtistName,\r\n    aba.AlbumName,\r\n    aba.Genre\r\nFROM ufn_AlbumsByArtist(1) aba\r\nINNER JOIN Artists ar\r\nON aba.ArtistId = ar.ArtistId;\r\n<\/pre>\n
Result:<\/p>\n
\r\n+--------------+-------------------------+---------+\r\n| ArtistName   | AlbumName               | Genre   |\r\n|--------------+-------------------------+---------|\r\n| Iron Maiden  | Powerslave              | Rock    |\r\n| Iron Maiden  | Somewhere in Time       | Rock    |\r\n| Iron Maiden  | Piece of Mind           | Rock    |\r\n| Iron Maiden  | Killers                 | Rock    |\r\n| Iron Maiden  | No Prayer for the Dying | Rock    |\r\n+--------------+-------------------------+---------+\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
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 … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7,5],"tags":[93,77,61,98,99],"class_list":["post-7785","post","type-post","status-publish","format-standard","hentry","category-sql","category-sql-server","tag-functions","tag-mssql","tag-t-sql","tag-table-valued-functions","tag-udf"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/7785","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=7785"}],"version-history":[{"count":6,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/7785\/revisions"}],"predecessor-version":[{"id":7791,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/7785\/revisions\/7791"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=7785"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=7785"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=7785"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}