User-Defined Functions in SQL: Expanding Your Database Toolkit

Why would you need to be able to create your own functions (UDF) in SQL? What is a typical function in SQL, and by extension, User-Defined Functions (UDF)?

Either as a seasoned SQL developer or random SQL user, it can't be overstated why you need to know how to create your own function.
Aside the fact that SQL developers write complex queries, design databases (table, schema, etc.), they also go advanced a bit into creating Stored proc and even complex functions to make their work an ease.

In order to avoid the need to define all the very basics, I will assume the reader is at least an immediate SQL user. But well, a beginner SQL analyst reading this would still gain one thing or the other. Before you read the last paragraph in this article, you would have known how to go about creating a User-Defined Functions (UDF), and some more.


A SQL function being a type of routine (program) is such that can be created to perform various tasks.

Broadly speaking, there are two types of functions:

1. Built-in Functions

2. User-Defined Functions (UDFs)

While Built-in Functions come in different kinds (Aggregate, Ranking, String, Date/DateTime, etc.), UDFs also their types, as we will see shortly.

By the way, let's go practical.

I will be using Microsoft SQL Server to do the demo in this article, while also using some data readily available online - AdventureWorksDW2014

Question:
Create a User-Defined Function to calculate total sum of sales for any specified product between two dates.

This is fairly simple one, but we will start somewhere.

Writing this as a function, it would look something like this:

CREATE FUNCTION dbo.CalcProductTotalSales
(@ProductKey INT, @start_date DATETIME, @end_date DATETIME)
RETURNS DECIMAL(18, 2)
AS
BEGIN
    DECLARE @UnitPrice MONEY;

    SELECT @UnitPrice = SUM(UnitPrice)
    FROM [dbo].[FactInternetSales]
    WHERE ProductKey = @ProductKey
    AND OrderDate BETWEEN @start_date AND @end_date;

    RETURN @UnitPrice;
END;

Sincerely speaking, it may not look so attractive, right?
Let me break it down:

1. Between BEGIN and END is the actual SELECT statement which does the analysis;
2. "CREATE" keyword is common in SQL; it is used to create objects such as TABLES, DATABASES, VIEWS, etc., in this regard we used it to create a FUNCTION, thus the FUNCTION keyword; and the function name is dbo.CalcProductTotalSales
3. Parentheses that follows function name specify the necessary variables to be made use of in the body of the function (between BEGIN and END keywords);
4. As many "@" seen in the function shows the individual variables needed within the function. "@" symbol is used to introduce variables in SQL Server.

While the function above is executed within SQL Server query editor (query window), it becomes stored away as seen below in the programmability folder within the database:

The implication of this is that even if SQL Server Management Studio (SSMS) is shut down, that particular function created remains permanent, and can continually be accessed, next time you restart your server.

But then, how do we then use it?
It is by simply using the usual SELECT statement, along with the function name, while supplying the parameters (productkey, start_date and end_date), as below:

SELECT dbo.CalcProductTotalSales(310, '2010-12-29 00:00:00.000', '2014-01-28 00:00:00.000');

If you recall the question to which we created User-Defined Function (to calculate total sum of sales for any specified product between two dates), you would know that, even without using a function approach, we can as well easily write a SELECT query to carry out our analysis. In fact, the SELECT statement below does same thing as our function:

SELECT SUM(UnitPrice) as total_revenue
FROM [dbo].[FactInternetSales]
WHERE ProductKey = 310
AND OrderDate BETWEEN '2010-12-29 00:00:00.000' AND '2014-01-28 00:00:00.000';

But then, why would we desire to create a function to an obvious SQL problem which needs a mere SELECT statement to solve?

• An obvious reason is, if there is no SQL built-in function readily available to solve a desired analysis in SQL, then User-Defined Function (UDF) can answer to the call. (The UDF example given above is such a simple one, used for illustration purpose)

• Secondly, if there be need to keep re-using a particular query in further future analysis, we can as well make a function of it. (SQL VIEWs and Stored Procedures share similar application as this, among many others).

• Lastly, UDFs help to simplify development by encapsulating (complex) business logic and make them easily available for reuse.

The advantage of using function includes:

• Code reusability

• Simplicity

• Consistency

• Modularity

Having introduced quite a straightforward function to prove a point, let's see more reason why you will come to a point where you will need to create your own function someday - if you have never hitherto!

Do you recall CAST() function?

This very function which converts one data type to the other? The reason why you don't get to create it afresh any time you need it is because it comes handy, already built-in in SQL and all you need to do is just to call it as at when needed.
If you were to create CAST function which can convert CHAR (string) to INT (integer) alone, error handling not factored in, it would look like this:

CREATE FUNCTION dbo.CastAsInt(@value NVARCHAR(MAX))
RETURNS INT
AS
BEGIN
    DECLARE @result INT;

    SELECT @result = CAST(@value AS INT);

    RETURN @result;
END;

Now, the fact that someday, you will need a function within SQL which is not natively available (or built-in), it is for this reason that it is essential to master how to craft your own UDFs, User-Defined Functions.
SQL creators knew that it would be impossible to factor in every functions one could ever think of as a built-in function, then the template to be able to construct one yourself is provided for.

Let us assume that we want to be able to calculate a person's age given the person's date of birth: that's, a formula which can calculate age from date of birth;
There is not any built-in function in SQL Server for such. What is close to this function is using the combination of DATEDIFF alongside GETDATE() to calculate the difference in YEAR.

(There is the AGE() function to calculate same in PostgreSQL. MySQL also has TIMESTAMPDIFF() function which is not that straightforward too)

To create a permanent function which can be used again and again in SQL Server, let's see how this can be done, we will call it dbo.CalculateAge:

CREATE FUNCTION dbo.CalculateAge(@dob DATE)
RETURNS INT
AS
BEGIN
    DECLARE @age INT;

    SELECT @age = DATEDIFF(YEAR, @dob, GETDATE()) - 
                  CASE 
                      WHEN MONTH(@dob) > MONTH(GETDATE()) OR (MONTH(@dob) = MONTH(GETDATE()) AND DAY(@dob) > DAY(GETDATE()))
                      THEN 1 
                      ELSE 0 
                  END;

    RETURN @age;
END;

No more, no less.

NB: The "dbo" is the very schema name which differentiates it from any other schema. It could be another name, but "dbo" (database owner) here is the default schema while the database/table was created.

If you supply any column or data (of date/datetime data type) to the function dbo.CalculateAge, it gives you the age on the go, without having to continually write the whole DATEDIFF, GETDATE functions again.
For instance,

What function can you think of again in SQL (SQL Server) which is not built-in? They can be many, depending on the use case.

Having said all these and explained why SQL developer should be able to create a UDF personally, let's briefly look at how to create one, making use of the template already provided in SSMS.
But foremost, this is worth mentioning, we have about 3 types of SQL User Defined Functions:

1. Scalar Functions (returns a single data value of the type defined in the RETURNS clause)

2. Table-Valued Functions (returns a table data type)

3. Inline Table_Valued Functions (returns a table data type)

To create any type of function using the templates inside SSMS,

• Right click the query editor and click Insert Snippet:

• Click on Function:

• Choose the type of function you desire to create based on what it returns (scalar, table)

• Choosing the first option (Create Inline Table Function):

There, you have the template. From the template, you can build your desired function.
(All the examples shown earlier are of Scalar type - because the function returns a single value)

Final Note & Considerations

There are a few considerations to keep in mind when creating User-Defined Functions (UDFs) in SQL Server:

• UDFs can’t be used to perform actions that modify the database state.

• Error handling is restricted in a UDF. A UDF doesn’t support TRY...CATCH, @ ERROR or RAISERROR1.

• UDFs can’t call a stored procedure but can call an extended stored procedure. In likewise manner, UDFs can’t return multiple result sets. Use a stored procedure if you need to return multiple result sets.

• UDFs can be nested; that is, one user-defined function can call another. The nesting level is incremented when the called function starts execution, and decremented when the called function finishes execution. In addition, user-defined functions can be nested up to 32 levels.
  
  

There you go! As you explore the more of the power of SQL data sleuthing, don't forget to consider what typical UDFs can do to aid your analyses.