SQL Table-Valued Function Example

Multi-line table valued function example

1. Introduction to SQL Table-Valued Function

In the past article we saw creating a simple scalar-valued function. Now, here in this article, we will look at building the Table-Valued function. Unlike the scalar-valued function a table-valued function returns a table to the caller. There are two kinds of table-valued functions we can write. Those are:

  1. In-Line Table-Valued functions
  2. Multi-line table valued functions

In this Example, we learn how to create both the functions. OK, let us start.

2. Inline Table-Valued Function Example

The inline table-valued function returns a table in-place of the scalar values. We can call table valued function as in-line when its body has a single select query. The function FuncAuthors is an example for the In-Line table valued function. The function is below:

In-Line table valued function
In-Line table valued function

We can execute the above T-SQL to create a sample Table-Valued function. After creating the function, it will appear in the Table-Valued function node as in the below picture:

Table-Valued Function in SQL Management Studio
Table-Valued Function in SQL Management Studio

Note that the function returns a table to the caller. It picks the columns au_id, au_lname etc., from the authors table. As this is the only statement in the function body, we call this function as an In-Line Table-Valued function. Moreover, we can also note that the function does not have Begin and End pair as the body contains a single statement. The below video shows creating an In-Line table valued function:


Video 1: Creating in-Line table valued function


3. Using the Inline-Table-Valued Function

Now we will try executing the function using the statement below:

We will get an error stating the function is not in the database. This is because the function that returns table cannot be used the same way we use the scalar-valued function.

In the last article, we saw the scalar-valued functions taking part in the select queries. As the scalar-valued function returns a single value, it can be in the select list & where conditions of the select queries. Whereas, the table-valued functions; as it returns a table can play in the ‘from’ clause of the select queries. Simply, wherever the table can show up in the SQL Select statement, the Table-valued function can be used. In our example, as the function is returning a Table and it is In-Line, we can even update author table through it. Now look at the below picture which shows correct usage of the table-valued function:

Executing the FuncAuthors Function
Executing the FuncAuthors Function

The above example shows calling the function in the from clause of the T-SQL select statement. In the column list, the above example specifies the * indicating all table columns returned by the function needs to be retrieved. We can also specify the columns returned by the function in the select list of the query. The below example shows forming the author’s full name in the select clause based on the table returned by the function:

Table valued function with Selective Columns
Table valued function with Selective Columns

Since the function FuncAuthors is an In-Line function, we can update the Authors table by making use of the table column returned by it. In the below example, we change the author’s last name from ‘White’ to ‘Gray’:

Update Table via In-Line table valued function
Update Table via In-Line table valued function

How to use inline scalar valued function is shown in the below.


Video 2: In-Line Table Valued function – Usage


4. Multi-Line Table-Valued Function Via Cursor

4.1 Multi-Line Function for Sales Order

One can go for Multi-line Table-Valued functions to handle the complex case of working out the needed result. Let us write an example which returns Order Information from the sales table looking at the sales data for a given order number. Once the sales order number is given, the function should return the Titles in the given ORDER, how many of each title placed in that order and total copies sold so for. Here we get the total sales by looking at all the orders.

4.2 Multi-Line Table-Valued Function Example

Have a look at the function below:

Multi-line table valued function example
Multi-line table valued function example

4.3 Variable Declarations

In the above T-SQL, we define the return table with columns and its data type as part of the SQL function itself (Marked as 1). We should define the data type in such a way that it should match with the underlying column of the DB table schema. Next, we declare two variables @storeid and @titleid  (Marked as 2) to retrieve the data from the relevant tables. For example, by using the storeId from the sales table, we retrieve the store name from the stores table. We use the four more variables (Marked as 3) to retrieve data for packing the return table. These four variables get the data from different tables of the Pubs database and we insert these values into the return table which you we will see soon.

4.4 Fetching Data Via Cursor

We know that the function takes the Order number as a parameter and we use it to declare the cursor SalesCur (Marked as 4) on the sales table. Then the cursor is opened (marked as 5) to fetch Title ID, Store ID and Quantity for the given order number from the sales table. There may be one or more rows (Or even zero) of data for the given order number. In the above T-SQL Example, the fetch statement pulls the data from the first row and moves the cursor pointer to the next valid row. We test the successful fetch using the SQL Environment @@FetchStatus and in our example, this test is used to iterate over the Cursor-Retrieved rows of data.

The possible values of Fetch status is shown in the below table:

0The FETCH was successful.
-1The FETCH failed, or the row was beyond the result set.
-2The row fetched is missing.

On every successful fetch, we use the fetched data @storedid, @titledid to retrieve the store name and title name from the corresponding tables stores, titles respectively (Marked as 6). Now we have @stname, @title and @qty (got from the cursor itself) for the return table. We got @TotalSold by making use of the aggregate function on the Sales table for the title. Once all the data are in hand, we insert them to the return table @OrderInfo (Marked as 7) and we return this table to the caller. Creating this function is explained in the below video:


Video 3: Creating Multi-Line Table-Valued Function


5. Using Multi-Line Table-Valued Function

Multi-Line table-valued function can be used the same way how we used the normal in-line table valued function. However, here we can’t perform an update. The first example is here below:

Multi-Line Table valued function Usage
Multi-Line Table valued function Usage
  1. The SQL Tree under the node Table-valued Functions (Marked as 1) shows the multi-line table valued function created in the past section.
  2. In the above example, the markings 2 and 3 show four books were in the order identified by the order number P3087a.
  3. The markings 4 and 5 show how we invoke the function and return the data. Note, as the function returns a table, we place it in the from clause of the select query. We can also note the data returned by GetOrderInfo function is a table @OrderInfo which is in its local scope.

Below is the second example, which shows how the aggregate is returned by the function.

Executing Multi-Line Table valued function Usage
Executing Multi-Line Table valued function Usage

Explanation

MarkingsExplanation
1 and 2The select query returns the sales data for the order number 722a. And we see only one book title (PS2091) is placed in that order.
3 and 4The query shows retrieving the sales data for the title_id PS2091. The result shows that there are 4 orders in which the book is ordered. We can also see that the total quantity ordered is 108 (By counting qty column in all four orders).
5 and 6The query makes use the GetOrderInfo function by passing the Order Number 722a. And you can see the result with TotalSold showing the 108.

Video 4: Using Our Multi-Line function


6. T-SQL Listings for the Examples in this Article

T-SQL Listing 1:

Listing 2:

T-SQL Listing 3:

Listing 4:

Do you like this Example? Please comment about it for others!!

This site uses Akismet to reduce spam. Learn how your comment data is processed.