SQL for Database Construction and Application Processing - Database Processing: Fundamentals, Design, and Implementation - page 321

INPUT
( parameter values )
from calling
SQL statement

PROCESS
( parameter values )
to create
( result value )

OUTPUT
( result value )
to calling
SQL statement

Figure 7-20
User-Defined Function
Logical Process Flow

MIX) that we discussed and used in Chapter 2, except that, as the name implies, we create
them ourselves to perform specific tasks that we need to do.
A common problem is needing a name in the format LastName, FirstName (including the
comma!) in a report when the database stores the basic data in two fields named FirstName
and LastName . Using the data in the VRG database, we could, of course, simply include the
code to do this in an SQL statement (similar to SQL-Query-CH02-38 in Chapter 2—see the
“By the Way” discussion on page 313 for a discussion of Oracle Database and MySQL concat-
enation methods) such as:

/* *** SQL-Query-CH07-01 *** */
SELECT RTRIM(LastName)+', '+RTRIM(FirstName) AS CustomerName,
AreaCode, PhoneNumber, Email
FROM CUSTOMER
ORDER BY CustomerName;

This produces the desired results, but at the expense of working out some cumbersome
coding:

The alternative is to create a user-defined function to store this code. Not only does this
make it easier to use, but it also makes it available for use in other SQL statements. Figure 7-21
shows a user-defined function written in T-SQL for use with Microsoft SQL Server 2012, and the
SQL code for the function uses, as we would expect, specific syntax requirements for Microsoft
SQL Server's T-SQL 2012:

● The function is created and stored in the database by using the T-SQL CREATE
FUNCTION statement .
● The function name starts with dbo , which is a Microsoft SQL Server schema name
(SQL Server schemas are discussed in Chapter 10A). This use of a schema name ap-
pended to a database object name is common in Microsoft SQL Server.
● The variable names of both the input parameters and the returned output value start
with @.

The concatenation syntax is T-SQL syntax.

●

The Oracle Database version of this function, which uses Oracle's PL/SQL, is discussed in
Chapter 10B, and the MySQL version, which uses MySQL's SQL/PSM standards, is discussed in
Chapter 10C.