Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
546 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
Written by Andrew Gould
In this tutorial
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.
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!
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
--Code goes here!
END
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
--Code goes here!
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
--Code goes here!
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.
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
--Code goes here!
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
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
--Code goes here!
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.
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
--Number of hours in a working day
,@HoursInDay AS INT
--Start time of a working day
--e.g. '09:00:00'
,@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)
--Check if call is finished on same day
SET @DaysDiff = DATEDIFF(DD,@StartTime,@EndTime)
IF @DaysDiff = 0
BEGIN
SET @TotalMins = DATEDIFF(MINUTE,@StartTime,@EndTime)
END
ELSE
BEGIN
--work out end time on first day
SET @FirstDayEnd =
DATENAME(YY,@StartTime)+'-'+
DATENAME(MM,@StartTime)+'-'+
DATENAME(DD,@StartTime)+' '+
@DayEndText
SET @FirstDayMins = DATEDIFF(MINUTE,@StartTime,@FirstDayEnd)
--work out start time on last day
SET @LastDayStart =
DATENAME(YY,@EndTime)+'-'+
DATENAME(MM,@EndTime)+'-'+
DATENAME(DD,@EndTime)+' '+
@DayStartText
SET @LastDayMins = DATEDIFF(MINUTE,@LastDayStart,@EndTime)
--work out number of full days
SET @TotalDays = (@DaysDiff - 1) * @MinsInDay
--add everything together
SET @TotalMins = @FirstDayMins + @LastDayMins + @TotalDays
--Work out number of weekend days (Sats and Suns)
SET @WeekendDays = DATEDIFF(WW, @StartTime, @EndTime) * 2
SET @WeekendMins = @WeekendDays * @MinsInDay
--Subtract weekend minutes from time
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
You can learn more about this topic on the following Wise Owl courses:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.