This final part of the series will show you how to create more complex
functions which perform several steps before returning an answer. Before
starting, you might want to refresh your memory on using
variables and
IF statements in
SQL.
The Example We'll Use
To demonstrate these techniques we'll create a function which extracts a
person's first name given their full name. Here's the basic logic of the
expression in a simple query:
The basic expression works in a simple query.
The CHARINDEX function calculates the position of the space
between a person's first and last name. For Tom Cruise
this gives the result of 4: the space is the fourth character in the name.
We then subtract 1 from this value and use the LEFT function to
take that many characters from the left of the person's name.
This expression works for most people, but our database has the added
complication of containing people with just a single name, such as Sting
and Cher. When we try to use the above expression and
include these people in the query things don't work out so well.
Pretentious actors - ruining databases since 1946.
When CHARINDEX doesn't find the space in a name it returns 0.
We then subtract 1 from this and try to get -1 characters from the left of the
name! We could solve this problem by making our expression more
complicated, but we're going to create a custom function to do it instead.
Of course, a well-designed database would never have included the first and
last name in a single column in the first place!
Defining the Function
The definition of this function is no more complicated than the
fnLongDate function we created earlier: it will accept a single
parameter with a text data type and return a single value, also with a text data
type.
CREATE FUNCTION fnFirstName
(
@FullName AS VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
END
Declaring and Setting Variables
Although we could make our function work with just a single, complicated
expression, it makes sense to take advantage of the programming capabilities of
SQL Server to break the calculation into separate, logical steps. To that
end we're going to use two variables to hold the intermediate answers in our
calculation.
CREATE FUNCTION fnFirstName
(
@FullName AS VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @SpacePosition AS INT
DECLARE @Answer AS VARCHAR(MAX)
END
We can use the first variable to hold the position of the space within a
person's full name.
CREATE FUNCTION fnFirstName
(
@FullName AS VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @SpacePosition AS INT
DECLARE @Answer AS VARCHAR(MAX)
SET @SpacePosition = CHARINDEX(' ',@FullName)
END
If the person's name contains a space, our variable will store its position;
if there is no space in the person's name it will store the value 0.
Using IF to Calculate Different Answers
Now we can use an IF statement to check what value our
variable holds. If it is 0 then we want the answer of our function to be
the person's full name, otherwise we want to extract their first name from their
full name.
CREATE FUNCTION fnFirstName
(
@FullName AS VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @SpacePosition AS INT
DECLARE @Answer AS VARCHAR(MAX)
SET @SpacePosition = CHARINDEX(' ',@FullName)
IF @SpacePosition = 0
SET @Answer = @FullName
ELSE
SET @Answer = LEFT(@FullName,@SpacePosition - 1)
END
Returning a Value
The last thing we must tell the function is to return the value stored in our
answer variable.
CREATE FUNCTION fnFirstName
(
@FullName AS VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @SpacePosition AS INT
DECLARE @Answer AS VARCHAR(MAX)
SET @SpacePosition = CHARINDEX(' ',@FullName)
IF @SpacePosition = 0
SET @Answer = @FullName
ELSE
SET @Answer = LEFT(@FullName,@SpacePosition - 1)
RETURN @Answer
END
After executing the code to create the function we can then use it in a
query.
Our function returns a sensible value for all of the people in the database.
Taking Things Further
The functions we have created in this series are relatively simple, but
there's no reason you couldn't create a much more complicated one yourself.
To give you an idea of what's possible the example shown below uses a custom
function which calculates the amount of working time which occurs between two
date and time values.
The function accepts four parameters: a start date and time, an end date and time, the number of hours in a working day, and the start time of a working day.
The function automatically discounts any non-working time between the start and
end of a job, including weekends. The code used to create the function is
shown below without further comment. Hopefully it's enough to give you
some inspiration for your own complex functions!
CREATE FUNCTION fnDuration
(
@StartTime AS DATETIME
,@EndTime AS DATETIME
,@HoursInDay AS INT
,@DayStartTime AS SMALLDATETIME
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @Result AS VARCHAR(MAX)
DECLARE @TotalMins AS INT
DECLARE @TotalHours AS INT
DECLARE @TotalDays AS INT
DECLARE @RemMins AS INT
DECLARE @MinsInDay AS INT
DECLARE @DaysDiff AS INT
DECLARE @FirstDayEnd AS DATETIME
DECLARE @LastDayStart AS DATETIME
DECLARE @FirstDayMins AS INT
DECLARE @LastDayMins AS INT
DECLARE @DayStartText AS CHAR(8)
DECLARE @DayEndText AS CHAR(8)
DECLARE @DayEndTime AS SMALLDATETIME
DECLARE @WeekendDays AS INT
DECLARE @WeekendMins AS INT
SET @TotalMins = 0
SET @MinsInDay = 60 * @HoursInDay
SET @DayStartText = CONVERT(CHAR(8),@DayStartTime,108)
SET @DayEndTime = DATEADD(HH,@HoursInDay,@DayStartTime)
SET @DayEndText = CONVERT(CHAR(8),@DayEndTime,108)
SET @DaysDiff = DATEDIFF(DD,@StartTime,@EndTime)
IF @DaysDiff = 0
BEGIN
SET @TotalMins = DATEDIFF(MINUTE,@StartTime,@EndTime)
END
ELSE
BEGIN
SET @FirstDayEnd =
DATENAME(YY,@StartTime)+'-'+
DATENAME(MM,@StartTime)+'-'+
DATENAME(DD,@StartTime)+' '+
@DayEndText
SET @FirstDayMins = DATEDIFF(MINUTE,@StartTime,@FirstDayEnd)
SET @LastDayStart =
DATENAME(YY,@EndTime)+'-'+
DATENAME(MM,@EndTime)+'-'+
DATENAME(DD,@EndTime)+' '+
@DayStartText
SET @LastDayMins = DATEDIFF(MINUTE,@LastDayStart,@EndTime)
SET @TotalDays = (@DaysDiff - 1) * @MinsInDay
SET @TotalMins = @FirstDayMins + @LastDayMins + @TotalDays
SET @WeekendDays = DATEDIFF(WW, @StartTime, @EndTime) * 2
SET @WeekendMins = @WeekendDays * @MinsInDay
SET @TotalMins = @TotalMins - @WeekendMins
END
SET @TotalHours = @TotalMins/60
SET @RemMins = @TotalMins % 60
SET @Result =
CAST(@TotalHours AS VARCHAR(MAX)) + ' hours, ' +
CAST(@RemMins AS VARCHAR(2)) + ' minutes'
RETURN @Result
END
