Scalar User Defined Functions in SQL Server - UDFs
Part five of a five-part series of blogs

If you have a calculation that you frequently use in SQL queries you're probably bored of writing out the same code time after time. Why not try creating a user-defined function to save you the hassle? This blog teaches you how to define your own custom functions, including how to use input parameters, how to alter a function after you've created it, and even how to use fancy programming techniques such as variables and IF statements to help structure complex sequences of calculations.

  1. Scalar User-Defined Functions in SQL Server
  2. Writing Custom Functions in SQL Server
  3. Creating and Using a Custom Function
  4. Altering and Deleting Custom Functions
  5. Writing Complex User-Defined Functions (this blog)

This blog is part of our long and detailed online SQL tutorial.  Another way to learn SQL is on one of our SQL training courses.

Posted by Andrew Gould on 01 February 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Writing Complex User-Defined Functions

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:

First name expression

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.

Error in expression

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

--Code goes here!

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

--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.

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

--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

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

--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.

Using complex function

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.

Duration function

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

This blog has 0 threads Add post