November 5, 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;

1 comment:

Leave your comment(s) here.

Like this site? Tell it to your Friend :)