Why would you?

Adding your own SQL Function can be a very useful thing to do. First of all to make life easier. Instead of writing the same code over and over again, you can simply call the SQL Function and get the same result.

The added benefit is that you can keep complex login in one single place. This makes your Queries more consistent and much easier to maintain. When the Business Logic needs to change in the future, you can simply modify it in one single place.

So besides making it easier for yourself, it is also a good practice to keep consistent and clean code.

How to add a SQL Function

Adding a SQL Function to your database is pretty easy. You just have to take care of these steps:

The Create Function Statement:
First you call for the Create Function Statement and give it an easy recognizable name. Keep in mind you want to use it later on so pick a name you can remember.
Add Parameters or not:
You can add one or more Parameters as input for the SQL Function between brackets. Again use names that are easy to recognize and remember. Each Parameter needs to start with the @ sign and you must specify the DataType.
What will be returned?:
Next you need to specify what will be returned as a value when the SQL Function is executed. This one also needs to have a DatType.
The Function itself:
Finaly you can put in anything you like to be executed in the SQL Function. Just be shure that you always return a Value from the Function. That can be done from anywhere in the Function but it is common practice to make this your last Statement in the Function.

Let's have a look at an example

Let's say that in your Employees Table you have a First Name which can be empty, a Middle Name which can be empty and a Last Name which is mandatory. Now you want to put these three together to form the Full Name of the Employee.

A good name for this Function would be GetFullName and it would require 3 Parameters. It would return a String containing the FullName.

Now you would need to add a space between the different parts. But since both the First and Middle Name are optional, you might end up with double spaces that you would need to remove. Also, if the First Name is empty, there would be a leading space that we want to remove. And just to be sure, we want to remove trailing spaces as well.

It is not a real difficult Function but it is easy to understand and read along. Here is what it would look like:

-- Create the Function:
create or alter function GetFullName(
  @FirstName nvarchar(30),
  @MiddleName nvarchar(10),
  @LastName nvarchar(30)
)
-- What it returns when executed:
returns nvarchar(100)
as
Begin
  -- What it does:
  declare @FullName nvarchar(100)

  set @FullName = concat(
    @FirstName,
    ' ',
    @MiddleName,
    ' ',
    @LastName
    )

  set @FullName = replace(@FullName, ' ', ' ')
  set @FullName = ltrim(@FullName)
  set @FullName = rtrim(@FullName)

  -- Return the Result:
  return @FullName
End
GO

This will create the SQL Function in the Database. Now let's see how we can use this one:

-- With Fixed Values:
select dbo.GetFullName('Jack', 'the', 'Ripper')

-- In a Select Statement
select dbo.GetFullName(FirstName, MiddleName, LastName) as FullName
from Employees

Which Functions did you create?

Before writing an SQL Function it can be useful to check whether it not already exist. Maybe you created it some time ago but forgot about it? Or maybe someone else already did?

You can check this in the sysobjects Table:

select *
from sysobjects
where xType = 'FN'
order by Name

Don't overdo it

Like I said, SQL Functions are great to keep complex Business Logic in one place so you can use it over and over again in any Query where it is needed. But it is an extra roundtrip inside the Query and that might impact performance.

So if it is something simple you might only need in one or two places, consider doing it inside your Query itself.

Use Functions only for complex Business Logic that is used frrequently in your Queries. Do not create Functions just because you can.

That's it for today!

SQL Functions are pretty useful to keep your code consistent and clean. Used with care they can be a great addition to your SQL Skills.

If someone is building an application on your Database, make sure you tell the developers they can use the SQL Functions as well. This will make sure that the Business Logic from the Database is also applied in the Application.

Just don't overdo it and always check the impact on the performance. Other than that, have fun with this one!

You can leave a comment or send a question to andre@andrespeek.com. Let me know if you got inspired and stay safe and healthy always!