Thursday, November 26, 2015

SQL 2005 - Creating and using Table-Valued Functions

1. Introduction

In the previous article we saw creating a simple scalar- valued function. Now, here in this article we will look at creating 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 that we can write. Those are:
1) In-Line Table-Valued functions
2) Multi-line table valued functions

In this article, I will walk you through creating both the functions. OK, let us start.

2. In-Line Table valued function

The inline table-valued function returns a table in-place of the scalar values. We can call table valued function as in-line when the body has a single select statement. The function “FuncAuthors” is an example for the In-Line table valued function and the function is given in the below screen shot:

In-Line Table Valued Function Example
Fig 1. In-Line table valued function

Execute the above T-SQL to create the Table-Valued function. Once the function is created, it will appear in the Table-Valued function node as shown in the below picture:

SQL Tree
Fig 2. Function in the tree

Note that the function returns a table by Picking the columns au_id, au_lname etc., from the authors table. As this is the only statement we call this function as an in-line table valued function also we can note that the function does not have Begin and End pair as the body contains 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 try executing the function using the way we executed the scalar valued function using the statement below:
-- InCorrect Usage
Select dbo.FuncAuthors();

You will get an error stating the function is not found 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 participating in the select queries. As the scalar-valued function returns a single value, it can participate on the select list, where conditions of the select queries. Whereas, the table valued functions; as it returns a table, can participate in the “from” clause of the select queries. Simply, wherever the table can appear in the SQL Select statement, the function (Table-valued) can be used. In our example, as the function is returning a Table and function is in-line, the author table can be even updated through it. Now look at the below picture which shows correct usage of the table valued function:

Using In-line Table-Valued Function
Fig 3. Using the In-Line 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 full name:

In-Line Table valued function with Selective Column
Fig 4. In-Line Table valued function with Selective Column

Since the function “FuncAuthors” is an In-Line function, we can update the authors table by making use of the table returned by it. In the below example, Author’s last name is changed from ‘White’ to ‘Gray’:

Update through In-Line Table-Valued function
Fig 5. In-Line table valued function in Update

Using the inline scalar valued function is shown in the below.

Video 2: In-Line Table Valued function – Usage

4. Multi-line Table valued function

Multi-line table valued functions are written to handle the complex situation of the achieving the required result. Let us write an example which returns Order Information from the sales database for a given order number. Once the sales order number is given, the function should return the Titles in the given ORDER, how many qty of each title placed in the order and total copies sold so for. Have a look at the function below:

Multi-line table valued function
Fig 6. Multi-line table valued function example
In the above T-SQL, the return table with column and data type is defined 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 table schema. Next, two variables @storeid and @titleid are declared (Marked as 2) to retrieve the corresponding data from the relevant tables. For example, by using the storied from the sales table, the store name is retrieved from the stores table. The other four more variables (Marked as 3) are used to retrieve the return table data from different tables of the pubs database. These four values are inserted into the return table that you we will see soon.

We know that the function takes the Order number as parameter and this parameter is used to declare the cursor SalesCur (Marked as 4) on the sales table. Then the cursor is opened (marked as 5) to retrieve 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 and the fetch statement retrieves the data from the first row and advances the cursor pointer to next valid row. The successful fetch is tested using the SQL Environment “@@FetchStatus” and in our example, this test is used to iterate over the retrieved rows of data.

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

The FETCH statement was successful.
The FETCH statement failed or the row was beyond the result set.
The row fetched is missing.

On every successful fetch, the fetched data @storedid, @titledid are used 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. The @TotalSold is retrieved by making use of the aggregate function on Sales table for the given title. Once, all the data in hand, there are inserted to the return table @OrderInfo (Marked as 7) and this table is returned to the caller. Creating this function is explained in the below video:

Video 3: Creating Multi-Line function

5. Using the Multi-Line table valued function

Multi-Line table-valued function can be used the same way normal in-line table valued function (Update not permitted here). Let us see the first example which is shown below:

Multi-Line Table valued function Usage
Fig 7. Multi-Line Table valued function Usage

1) The multi-line table valued function created in the first section is shown in the sql tree under the node Table-valued Functions (Marked as 1).
2) In the above example, the markings 2 and 3 shows four books are placed in the order identified by the order number P3087a.
3) The markings 4 and 5 show how the function is invoked and its returned data. Note that as the function returns the table it is placed in the “from” clause of the select query. We can also see the return data is from the @OrderInfo table returned by the function @GetOrderInfo.

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

Multi-Line Table valued function Usage
Fig 8. Multi-Line Table valued function Usage


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.

The demo of above two examples are given in the below video.

Video 4: Multi-Line function usage

6. Listings

Listing 1:
-- 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:
-- 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';

Listing 3:
-- 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
                --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
                                --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;

Listing 4:
-- 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');

Like this site? Tell it to your Firend :)

*** When a New Article posted, get Notification to the email that you give below: ***

Follow us on G+

C++ Articles