Emil Drkusic
The data model we'll use to explain user-defined functions

Learn SQL: User-Defined Functions

February 25, 2020 by

You can create several user-defined objects in a database. One of these is definitely user-defined functions. When used as intended, they are a very powerful tool in databases. In today’s article, we’ll see how to create, change and remove them from the database, as well as how to use them. So, let’s dive into the matter.

SQL Server Objects

As mentioned in the introduction, there are different kinds of objects you could create in the database. Besides tables and keys, other well-known objects are procedures, triggers, and views. And, of course, user-defined functions, which are today’s topic. The main idea behind objects is to have them stored in the database and avoid writing the same code over and over again. Also, you can control what is the input and define the structure/type of output. And last, but not least, you can define permissions to decide who’ll be able to use them and in what way he’ll be able to do it.

We’ll describe all of them in the upcoming articles, but in this article, we’ll focus only on the user-defined functions.

The Model

Let’s remind ourselves of the model we’re using in this article series.

The data model we'll use to explain user-defined functions

This time we won’t use this model so extensively as before, because of the nature of the functions we’ll declare, but also because of the complexity of queries we’ll use (they’ll be much simpler). While our queries will be simple, there is no reason why you wouldn’t use user-defined functions in much more complex queries as well.

CREATE/ALTER/DROP User-Defined Function

Whenever you’re working with database objects, you’ll use these commands – CREATE (new), ALTER (existing), and DROP (existing). The syntax goes something like CREATE/ALTER/DROP <type of the database object> <object name> AS. This differs slightly regarding the object type and also if you are creating, modifying or deleting the object.

For user-defined functions, these syntaxes look as follows:

Most things should be pretty obvious here. The function:

  • Takes parameters as input
  • Does something with these input values (SQL statements). Technically it will use values provided as parameters and combine them with other values (local variables) or database objects and then return the result of these combinations/calculations
  • Returns result of the calculation (RETURN value) with the previously defined type (RETURNS data_type)

ALTER is very similar to CREATE and it simply modifies the existing function. To delete a function, we’ll use statement DROP FUNCTION and the name of that function.

  • Note: If we would work with procedures, we would use CREATE PROCEDURE, ALTER PROCEDURE, and DROP PROCEDURE.

A Simple User-Defined Function

It’s time that we create our first and pretty simple user-defined function. We want to list all cities and write down are they east or west when compared to London (longitude = 0). Cities east of London will have positive city.long values, while those west of London will have this value negative.

We’ll use the following code to create the function:

The first thing we should notice, after running this command, is that our function is now visible when we expand “Scalar valued functions” in the “Object Explorer” (for the database where we’ve created this function).

Object explorer scalar-valued functions

Our function takes a number as a parameter. The return value must be of the CHAR(4) type. The initial value (variable @return_value) is initially set to ‘same’. If the parameter (variable @long) is greater than 0, we’re ‘east’ from London, and if it’s less than 0, we’re ‘west’ of London. Notice that, in case of @long was 0, none of these two Ifs will change the value, so it will hold the initial value -> ‘same’.

This is really a simple function, but it’s a nice way to show what functions can do.

Let’s now see how we can use this function inside a query. To achieve that, we’ll use the following simple select statement:

The result is shown in the picture below.

Testing function

You can easily notice that we’ve called function 3 times in the same select, and the output was as expected. This was actually testing if our function is working as expected.

  • Note: You’ll call a function by simply using its name and providing the parameters needed. If the function is value-based, then you’ll be able to use this function at any place where you would use a number, string, etc.

Now, we’ll use this function in the more complex query:

SELECT query result

The important thing to notice here is that we’ve used function as a “column” in our select query. We’ve passed parameter (city.long of the related row) and the function returned a result of the calculation. This is great because we’ve avoided writing complex calculations in a select query, and also, we can reuse this function later in any other query.

    Note: Creating a user-defined function has few advantages:

  • Complex code is stored in one structure. You can later look at that structure as on the black box, where you’re only interested in passing appropriate values as parameters and the function will do the rest
  • You can much easier test input parameters using IF or CASE, and even use loops in the functions. This is sometimes very hard (sometimes impossible) to simulate directly in SELECT statements
  • Once you create a function, and after it’s properly tested, you don’t have to bother later is it working as expected and you’re avoiding a possibility to make an error because you’re not rewriting the same code over and over again (not to mention that you’ll use less time when not rewriting the same code)
  • If you need to make changes to your code, you’ll do it in one place and it will reflect at every place this function is used

A User-Defined Function Returning the Table

Let’s now examine a more complex function. This time we want to pass long as an argument and we’ll expect that function returns a table of all cities ‘east’ from the given parameter.

We’ve created the following function:

You can also see that function listed in the “Table-valued Functions” section in the “Object Explorer”.

Object explorer table-valued functions

Now, we’ll use the function.

User-defined function used in the FROM part of the SELECT query

You can notice that we’ve used the function as a table (it’s a table-valued function, so this sounds pretty logical 🙂 ).

An idea to implement

I won’t do it now, but just throwing out an idea. This is something that’s doable in other ways (GROUP_CONCAT or simulating it), but the function and loops would really help here a lot. So, the thing we want to do is following – Write down a function that shall, for a given city.id, find all cities east and west from that city. The function shall return a string like “east: <names of all cities east from the given city>; west: <names of all cities west from the given city>”. You should use a loop in this function. You can Google it or wait for our article related to loops in SQL Server.

Conclusion

User-defined functions are a very powerful tool. You should use them when you’ll have a calculation you’ll repeat throughout your database. E.g. calculating the tax on different products based on predefined rules (that can change during the time), is one good candidate for the function. You put all rules there, pass parameters to the function, and as a result get the desired number. But, as with everything else, do not overuse them.

Table of contents

Learn SQL: CREATE DATABASE & CREATE TABLE Operations
Learn SQL: INSERT INTO TABLE
Learn SQL: Primary Key
Learn SQL: Foreign Key
Learn SQL: SELECT statement
Learn SQL: INNER JOIN vs LEFT JOIN
Learn SQL: SQL Scripts
Learn SQL: Types of relations
Learn SQL: Join multiple tables
Learn SQL: Aggregate Functions
Learn SQL: How to Write a Complex SELECT Query?
Learn SQL: The INFORMATION_SCHEMA Database
Learn SQL: SQL Data Types
Learn SQL: Set Theory
Learn SQL: User-Defined Functions
Learn SQL: User-Defined Stored Procedures
Learn SQL: SQL Views
Learn SQL: SQL Triggers
Learn SQL: Practice SQL Queries

Emil Drkusic
Latest posts by Emil Drkusic (see all)
SQL commands, T-SQL

About Emil Drkusic

Emil is a database professional with 10+ years of experience in everything related to databases. During the years, he worked in the IT and finance industry and now works as a freelancer. His past and present engagements vary from database design and coding to teaching, consulting, and writing about databases. Also not to forget, BI, creating algorithms, chess, philately, 2 dogs, 2 cats, 1 wife, 1 baby... You can find him on LinkedIn View all posts by Emil Drkusic

302 Views