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

&5($7( )81&7,21 GER 1DPH&RQFDWHQDWLRQ

7KHVHDUHWKHLQSXWSDUDPHWHUV
#)LUVW1DPH &+$5

#/DVW1DPH &+$5

5(785169$5&+$5
$6
%(*,1

7KLVLVWKHYDULDEOHWKDWZLOOKROGWKHYDOXHWREHUHWXUQHG
'(&/$5( #)XOO1DPH 9$5&+$5

64/VWDWHPHQWVWRFRQFDWHQDWHWKHQDPHVLQWKHSURSHURUGHU
6(/(&7 #)XOO1DPH 575,0 #/DVW1DPH 575,0 #)LUVW1DPH

Figure 7-21
User-Defined Function
Code to Concatenate
FirstName and
LastName

5HWXUQWKHFRQFDWHQWDWHQDPH
5(7851 #)XOO1DPH

(1'

Now that we have created and stored the user-defined function, we can use it in
SQL-Query-CH07-02:

/* *** SQL-Query-CH07-02 *** */
SELECT dbo.NameConcatenation(FirstName, LastName) AS CustomerName,

AreaCode, PhoneNumber, Email

FROM CUSTOMER

ORDER BY CustomerName;

Now we have a function that produces the results we want, which of course are identical
to the results for SQL-Query-CH07-01 above:

The advantage of having a user-defined function is that we can now use it whenever we
need to without having to re-create the code. For example, our previous query used data in
the View Ridge Gallery CUSTOMER table, but we could just as easily use the function with the
data in the ARTIST table:

/* *** SQL-Query-CH07-03 *** */
SELECT dbo.NameConcatenation(FirstName, LastName) AS ArtistName,

DateofBirth, DateDeceased

FROM ARTIST

ORDER BY ArtistName;