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.
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE FUNCTION [database_name.]function_name (parameters) RETURNS data_type AS BEGIN SQL statements RETURN value END; ALTER FUNCTION [database_name.]function_name (parameters) RETURNS data_type AS BEGIN SQL statements RETURN value END; DROP FUNCTION [database_name.]function_name; |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE FUNCTION east_or_west ( @long DECIMAL(9,6) ) RETURNS CHAR(4) AS BEGIN DECLARE @return_value CHAR(4); SET @return_value = 'same'; IF (@long > 0.00) SET @return_value = 'east'; IF (@long < 0.00) SET @return_value = 'west'; RETURN @return_value END; |
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).
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:
|
1 |
SELECT dbo.east_or_west(0) AS argument_0, dbo.east_or_west(-1) AS argument_minus_1, dbo.east_or_west(1) AS argument_plus_1; |
The result is shown in the picture below.
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:
|
1 2 |
SELECT *, dbo.east_or_west(city.long) FROM city; |
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.
- 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
Note: Creating a user-defined function has few advantages:
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:
|
1 2 3 4 5 6 7 8 |
CREATE FUNCTION east_from_long ( @long DECIMAL(9,6) ) RETURNS TABLE AS RETURN SELECT * FROM city WHERE city.long > @long; |
You can also see that function listed in the “Table-valued Functions” section in the “Object Explorer”.
Now, we’ll use the function.
|
1 2 |
SELECT * FROM east_from_long(0.00); |
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
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
- Learn SQL: How to prevent SQL Injection attacks - May 17, 2021
- Learn SQL: Dynamic SQL - March 3, 2021
- Learn SQL: SQL Injection - November 2, 2020