Transcript Creating Functions - Western Washington University

Creating Functions
If you came to this presentation via a web browser,
right-click and choose “Full Screen” before proceeding.
Click mouse or press space bar to continue.
• This presentation was prepared by Professor Steve Ross, with the advice of other MIS Faculty, for use in MIS Classes at Western Washington University.
Please contact Dr. Ross for permission to use in other settings..
The Challenge
• Create functions that
display a person’s
name in a single
field:
– Last, First Middle
– First Middle Last
• Functions should
accommodate
possibility of missing
or null First and
Middle Names
tblWorker
PK,I3
WorkerID
LastName
FirstName
MidName
FK1,I2,I1 SupervisorID
AnnualPayIn1000
FK2,I5,I7 RankID
FK3,I6,I4 DepartmentID
Design Before You Build
• What fields – from what tables – are required?
– tblWorker
• LastName – Data Type: Text (30)
• FirstName – Data Type: Text (30)
• MidName – Data Type: Text (30)
• What additional characters (spaces,
punctuation) are required?
– Space after each non-blank first or mid name
– Comma after last name when followed by non-blank
first name
Creating the Function, Step 1
• Functions are contained under Programmability
in the database
– Expand Programmability, then Functions
– We will create a
Scalar-valued Function
– Right-click Scalar-valued
Function, then choose
New Scalar-valued Function…
(see next slide)
Creating the Function, Step 2a
• Start by reading the note at the top of the
screen that opens
------------
================================================
Template generated from Template Explorer using:
Create Scalar Function (New Menu).SQL
Use the Specify Values for Template Parameters
command (Ctrl-Shift-M) to fill in the parameter
values below.
This block of comments will not be included in
the definition of the function.
================================================
Creating the Function, Step 2b
• Press Ctrl-Shift-M* and enter parameters:
– Begin the function name with the “fn” prefix
– Parameters and variables start with @ sign
– We will add more parameters after pressing OK
* Or select Query | Specify Values
for Template Parameters
Creating the Function, Step 2c
• Add the other parameters, normal practice is to
put each on a separate line, separated by
commas. “--” denotes a comment.
-- =============================================
-- Author:
Steve Ross
-- Create date:
14-Jan-10
-- Description:
Formats names: Last, First Middle
-- =============================================
CREATE FUNCTION fnNameLast
(
-- Add the parameters for the function here
@first varchar(30),
@middle varchar(30),
@last varchar(30)
)
Creating the Function, Step 2d
• Assign the value to be returned to the result
variable:
RETURNS varchar(60)
AS
BEGIN
-- Declare the return variable here
DECLARE @Result varchar(60)
-- Add
here
SELECT
SELECT
SELECT
the T-SQL statements to compute the return value
@Result = @last
@Result = @Result + ', ' + @first
@Result = @Result + ' ' + @middle
-- Return the result of the function
RETURN @Result
END
GO
Creating the Function, Step 2e
• Execute the query, which should create the
function.
• Right-click the database name, choose Refresh
• Your function should appear in the list:
Testing the Function I
• Click the New
Query button
• Ensure that your
database is
selected
• Type a simple
SELECT
statement
• Execute the
statement
Testing the Function II
• Modify the SELECT
statement to refer to
fields from a table
• Execute the
statement
Appendix:
Bullet-proofed Version
This version handles problems that can arise when the data (names) contain Null or empty strings
BEGIN
-- This variable will hold the fullname
DECLARE @FullNameBP varchar(75)
-- Concatenate the name elements: first middle last
-- Set @FullName to '', otherwise it will contain NULL
SELECT @FullName = '' – Two single quotation marks
IF (@FirstName IS NOT NULL)
SELECT @FullName = @FullName + @FirstName
-- Add [space] and middle name
-- Trim leading [space] in case @FullName is still empty
IF (@MidName IS NOT NULL) AND (@MidName <> '')
SELECT @FullName = LTRIM(@FullName + ' ' + @MidName)
-- Add [space] and last name
-- Trim leading [space] in case @FullName is still empty
IF (@LastName IS NOT NULL) AND (@LastName <> '')
SELECT @FullName = LTRIM(@FullName + ' ' + @LastName)
RETURN @FullName
END