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');

Thursday, November 05, 2015

SQL 2005 - Creating and using Scalar-Valued function in SQL Server

1. Introduction

Stored Procedure and function in SQL server are the compiled unit of SQL Program which performs a specific task. Unlike a stored procedure, a user defined function in SQL server can participate on the SQL Select Queries. In this article, I will show creating and using the “Scalar-Valued Function” (UDF) in the SQL Server.

The Examples in this article are created using Microsoft supplied Titles table of Pubs database. You can download the SQL Script that creates the Pubs and NorthWnd database from the SQL 2005 Section of this site. Before we begin, here is the content of the Titles table that you can refer while going through the article:

Content of Titles Table
Fig 1. Content of Titles Table

2. Simple Scalar-Valued Function

Just like a stored procedure the SQL Server function also performs a specific task and returns value to the caller. Inside the body of the function, the return value to the caller is decided. Once the function is created, it can be used in the same way how one can use the in-build functions of SQL Server.

The below picture shows set of T-SQL for creating a user defined function called TotalBooks. This function counts number of titles from the Pubs.Title table and returns that information to the caller.

Simple Scalar-Valued Function Example
Fig 2. Simple Scalar-Valued Function Example

Just like a stored procedure, the keyword “Create Function” is used to create the function (Marked as 1) with the name (Marked as 4) you like. Since the function returns a value to the caller, the keyword “Returns” is used to specify type of data returned by the function. In our Example, the function TotalBooks returns an integer data type (Marked as 2).

In the body of the function, the duties of the function and what it should return to the caller are decided. The body of the function goes in between the “Begin & End” pair. In our Example, the variable BookCount (Marked as 5) is declared as integer. Total number of the title is computed by using a Count aggregate function and the count is assigned to the internal member (Marked as 6) called @BookCount. Then this book count is returned (Marked as 3 and 7) to the caller of the function. Notice the use of “Returns” (Marked as 2) and “Return” (Marked as 3) where we usually make mistake. Once the function is created, you can verify it by expanding the “Scalar-Valued Function” node under the “Programmability” node of the Database as shown in the below picture.

User Function in Database Tree
Fig 3. User Function in Database Tree

While executing the function, the function name should be qualified with the schema name (In our case it is the default dbo schema). Also do not forget to place the parenthesis after the function name. The function can be executed as follows:

Executing the Scalar-Valued Function
Fig 4. Executing the Scalar-Valued Function

The below given video shows creating and executing the Function which is  discussed above.
Video 1: Simple Scalar-Valued Function

3. Scalar-Valued Function with Parameters and Default Parameter

In the previous section, we had created a simple "Scalar-Valued function". A Scalar-Valued function can take parameter also. In this section we will create a function that returns a value which is the total of all titles that belongs to a given book section. To do this, the function will expect a “Book Section” as parameter from the caller. Have a look at the below example:

Scalar-Valued function with Parameter
Fig 5. Scalar-Valued function with Parameter

Here, we are creating a function called TotalPrice which accepts a parameter called “Type” (Marked as 1). This parameter evaluates to NULL when the caller does not supply value to it. Note that this parameter type should match with the datatype of “Type” column in the Title table as we may supply the parameter value from table column also. Since the Total Price is retrieved from the Price column of the Title table, the return type is given as Money (Marked as 1) again matching with the corresponding table column type. The “Null on Null Input” states that, the function returns immediately when one or more null parameter is encountered in the parameter list (Marked as 2).

Other part of the function is similar to what we have written in the previous section. However, you can see how the parameter passed-in is used inside the body of the function. The parameter passed is used in the where-clause of the select query formed inside the Function body (Marked as 3). Here, the total price of book section specified by the parameter is retrieved and assigned to the local variable @PriceTotal. This variable is then returned to the caller of the function.

4. Passing Parameters to Scalar-Valued Function

In the above example, you may think leaving empty in the parameter list will supply the default value of null. But, that is not right in case of SQL Server 2005. Have a look at the Query and the Error message below:

Select Dbo.TotalPrice();

Msg 313, Level 16, State 2, Line 1
An insufficient number of arguments were supplied for the procedure or function Dbo.TotalPrice.

The correct way of supplying the default parameter is shown below:

Select Dbo.TotalPrice(Default);


The above query returns null as it encounters a null parameter. Look at the example and section marked as 2. Other parameter passing techniques are explained through a picture shown below:

Fig 6. Executing Scalar-Valued function with Parameters

In the First highlighted portion of the picture, you can see the function called two times by the caller by supplying the char constants two times (mod_cook and business). The function returns the Total price value for the sections mod_cook and business.

The second highlighted section shows passing the parameter from the table itself. Here, the Type column of the Title table is passed as the parameter to the function.

The third highlighted section shows the result of executing the query. The query returns 6 rows (As we ask for distinct of Type). Hence the function is invoked 6 times and each time supplying the value of type column, which retrieved from the table "Title".

Video 2: Scalar-Valued function with Parameter

Script Listing

Listing 1.1

--Simple Function
Create Function TotalBooks()
returns int as
     declare @BookCount as int;
     Select @BookCount = Count(Title_id)
     from Titles;
     return @BookCount;

Listing 1.2

--Function with Parameter
Create Function TotalPrice(@Type char(12)= null)
returns money
with returns null on null input as
     declare @PriceTotal money;
     Select @PriceTotal=SUM(Price)
     From Titles
     Where Type=@Type;
     return @PriceTotal;

Like this site? Tell it to your Firend :)