557 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls 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
Scalar User Defined Functions in SQL Server - UDFs
Part one 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.
In SQL Server, a scalar function is one which returns a single value, be that a string of text, a number, or a date. There are many built-in functions in SQL Server, but this blog will teach you how you can write your own user-defined functions, or UDFs.
You may have used existing functions in your SQL queries in order to create calculated fields, such as in the example below:
--The length of the film name
--Replaces NULL with custom value
,ISNULL(FilmName, 'No title entered')
--The weekday of the date
--Date in UK format
--Converts one data type into another
,CAST(FilmRunTimeMinutes AS DECIMAL)/60
All of the functions used in the example above perform a useful role and there are many more built-in functions available:
Every SQL Server database has a Programmability folder giving you access to the built-in, system functions.
Although there are many system functions available, what happens if you can't find a function that meets your needs? In the query shown below we've used the built-in DATENAME function to create a custom format for the dates in a field:
,DATENAME(DW,FilmReleaseDate) + ' ' +
DATENAME(D,FilmReleaseDate) + ' ' +
DATENAME(M,FilmReleaseDate) + ' ' +
The four calls to the DATENAME function combine to create a single calculated column, as shown below:
The result is a neatly formatted column of dates.
Although the expression in the query works, it's a little long and unwieldy to be typing in every time I want to format dates in that particular way. Ideally, I'd like a function which encapsulates the logic of my expression which I can use in my queries in the same way as a built-in function. Unfortunately, no such function exists in SQL Server. The good news, however, is that you have the option to create your own custom functions! Read part 2 of this blog series to discover two ways to create a user-defined function.
Although scalar user-defined functions can be immensely useful, they can have a dramatic negative impact on query performance. Before you embark on a crusade to convert all of your inline expressions to custom functions it might be worth a quick Google of the phrase sql user defined function performance. Of course, if you're anything like me, after reading all the advice you'll create a UDF anyway and test its performance for yourself!
|Parts of this blog|
25 Aytoun Street