T-SQL 101: 114 Selecting from Table-Valued Functions

In previous posts, we’ve seen how to query tables. Another type of object that you might need to query is a table-valued function.

Table-valued functions (TVFs) are predefined code i.e., somebody’s written the code, and they take parameters, but they return row set of data, much like a table.

So in the example above, I’m saying I want to declare a Cinema ID variable, and I’ve said let’s have Cinema 27. Then I’ve said I wanted to select from GetRecentOrders. It’s  a TVF that takes two parameters: the Cinema ID, and the maximum number of orders to return. In the case, I’ve said that I want the last two orders for cinema 27.

This didn’t need to use a variable. I just happened to use one.

The function has defined all of the columns that are coming back. That was done in the definition of the function. Note that I can use the function, without knowing how it works. That’s the upside of abstraction. There might be serious complexity in there. The downside is a potential performance issue but we’ll talk about that later.

For now, it’s just important to know how to call functions that return row sets rather than just scalar values. You use them like tables or views. In fact, they look like views that have parameters.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2025-02-12