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:
- In-Line Table-Valued functions
- 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:
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:
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:
|
1 2 |
-- InCorrect Usage Select dbo.FuncAuthors(); |
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:
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:
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’:
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:
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:
| 0 | The FETCH was successful. |
| -1 | The FETCH failed, or the row was beyond the result set. |
| -2 | The 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:
- The SQL Tree under the node Table-valued Functions (Marked as 1) shows the multi-line table valued function created in the past section.
- In the above example, the markings 2 and 3 show four books were in the order identified by the order number P3087a.
- 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.
Explanation
| Markings | Explanation |
|---|---|
| 1 and 2 | The 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 4 | The 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 6 | The 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:
|
1 2 3 4 5 6 7 |
--◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙ -- Table-Valued Function Inline --◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙ Create Function FuncAuthors() Returns Table As Return Select au_id, au_lname, au_fname, phone, city, state, zip from authors; |
Listing 2:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
-- InCorrect Usage Select dbo.FuncAuthors(); -- Correct Usage Select * from dbo.FuncAuthors(); -- Correct Usage Select au_id, au_fname + ' ' + au_lname as AuName from dbo.FuncAuthors(); --Update through the Function -- Valid only for inline Select * from dbo.FuncAuthors() Where Au_id = '172-32-1176'; Update dbo.FuncAuthors() Set Au_Lname = 'Gray' Where Au_id = '172-32-1176'; Select * from dbo.FuncAuthors() Where Au_id = '172-32-1176'; Update Table Authors Set Au_Lname = 'White' Where Au_id = '172-32-1176'; Select * from dbo.FuncAuthors() Where Au_id = '172-32-1176'; |
T-SQL Listing 3:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
--◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙ -- Table-Valued Function Multi-line --◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙◙ Create Function GetOrderInfo( @OrderNumber varchar(20)) returns @OrderInfo table( storeName varchar(20), title varchar(80), Qty smallint, TotalSold int ) as Begin --For Data retrival declare @storeid char(4); declare @titleid varchar(6); declare @stname as varchar(20); declare @title as varchar(80); declare @qty as smallint; declare @TotalSold as int; -- Cursor to hold all the titles and store id declare SalesCur cursor for select stor_id, title_id, qty from sales where ord_num = @OrderNumber; -- Open cursor and Fetch data Open SalesCur; Fetch next from SalesCur into @storeid, @titleid, @qty; while @@Fetch_status = 0 begin --Collect data for the return table select @stname = stor_name from stores where stor_id = @storeid; select @title = title from titles where title_id = @titleid; Select @TotalSold = sum(qty) from Sales where title_id = @titleid; --Insert data to the return table insert @OrderInfo (storeName, title, Qty, TotalSold) Select @stname, @title, @qty, @TotalSold; Fetch next from SalesCur into @storeid, @titleid, @qty; end return end |
Listing 4:
|
1 2 3 4 5 6 7 |
-- Executing the Function -- Remember there is no validation Select * from sales where ord_num = 'P3087a'; Select * from dbo.GetOrderInfo('P3087a'); Select * from sales where ord_num = '722a'; Select * from sales where title_id = 'PS2091'; Select * from dbo.GetOrderInfo('722a'); |
Categories: SQL Server
Tags: In-Line Table-Valued Function, Multi-Line Table-Valued Function, SQL Cursor, TSQL Function Return Table
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.