A function is a set of SQL statements that perform a specific task. Functions foster code reusability. If you have to repeatedly write large SQL scripts to perform the same task, you can create a function that performs that task. Next time instead of rewriting the SQL, you can simply call that function. A function accepts inputs in the form of parameters and returns a value. SQL Server comes with a set of built-in functions that perform a variety of tasks.

Of course, you could create a stored procedure to group a set of SQL statements and execute them, however, stored procedures cannot be called within SQL statements. Functions, on the other hand, can be. Also, another issue with functions is that they have to be called for each row. Therefore, if you are using functions with large data sets, you can hit performance issues.

Let’s work through a simple example.

Preparing the data

First, let’s create some dummy data. We will use this data to create user-defined functions.

This script will create the database “schooldb” on your server. The database will have one table with five columns i.e. id, name, gender, DOB and “total_score”. The table will also contain 10 dummy student records.

Built-in functions

As discussed earlier, SQL server adds some built-in functions to every database. To see list of built-in functions for your “schooldb” database, go to Object Explorer -> Databases -> schooldb -> Programmability -> Functions -> System Functions. This gives you the list of all built-in functions, as below.

Inside the System Function folder, built-in functions are grouped into different folders depending upon their functionality. For example, if you open the “Date and Time Functions” folder, as shown above, you will see all of the date and time-related functions. Expand any function and you will see the parameter’s type and the value returned by the function.

If you expand the “Datename” function you will see that this function accepts two parameters. The first parameter is the “Date part” which is of type varchar and the second parameter is “Expression” and that is a datetime type parameter. The function returns a varchar type value.

To see the Datename function in action; let’s create a query which selects the names and year of birth of the students. When we inserted the dummy records, we specified the complete date of birth of each student which included the year, month and year. However, using the datename function we will retrieve only the birth-year of a student. Take a look at the following query.

Notice that in the above query we have used the Datename function. We passed “YEAR” as date part and the DOB column as the datetime expression to the function. The above function will return the following.

User-defined functions

Built-in functions do not always offer the desired functionality. Take the “Datename” function that we saw in action in the previous section. Although it retrieves the date in multiple formats, what if you want to retrieve the date in a different format; one that is not supported by the “Datename” function. For example, what if we want to retrieve students’ date of birth (DOB) in format “Friday, 29 July 2009”? No built-in function retrieves date of birth in this format. To do this we would have to call the “Datename” function multiple times and rely on string concatenation in order to retrieve date in our desired format. Take a look at the following script that retrieves date in the format that we just discussed.

In the above query, we called “Datename” function four times. Each time we passed it a different date part. In the first call, we passed DW as the parameter which returns the day of the week, we then concatenated the result with another call to “Datename” function which returned day of the month. Similarly, we retrieved the month name and year name from the DOB column of student table. The above query retrieves following results.

Ideally, there should be a function that takes datetime expression as a parameter and returns the date in the desired format. Instead, we had to call “Datename” function four times.

This is where user-defined functions come handy. SQL Server allows users to create custom functions according to their exact requirements.

There are three types of user-defined functions in SQL Server:

1. Scalar Functions (Returns A Single Value)
2. Inline Table Valued Functions (Contains a single TSQL statement and returns a Table Set)
3. Multi-Statement Table Valued Functions (Contains multiple TSQL statements and returns Table Set)

In this article we will look at user-defined scalar functions only.

Creating user-defined functions

Let’s us create a function named “getFormattedDate”. This function accepts a datetime type value and returns a varchar value which is actually our formatted date.

Open a new query window and execute the following script.

Here the script starts with the “USE schooldb” command because we want to create this function inside “schooldb” database. Next, we write a “Go” statement to create a new batch statement. Function declaration in SQL server always starts with CREATE FUNCTION. The parameters passed to the function are specified inside the opening and closing parenthesis that follows the function name.

In the above script, we create a function “getFormattedDate” which accepts one parameter @DateValue of type DATETIME. After that, the return type of the function is specified which is VARCHAR(MAX) in our case. Finally, the body of the function is defined inside BEGIN and END statements. Here, in the body of “getFormattedDate” function, we are creating formatted date by calling “Datename” function multiple times.

Now, to see if this function has actually been created, go to Object Explorer -> Databases -> schooldb -> Programmability -> Functions -> Scalar-Valued Functions. Here you should see your newly created function. If you expand the function, you will see the parameter that the function takes. Take a look at the following screenshot for reference.

Testing a user-defined function

Let’s see if the function is working correctly. Open a new query window and execute the following script.

Here we are using a SELECT statement to retrieve the values for the name and DOB columns of the student table. To format the datetime values in DOB column, we are using “getFormattedDate” function that we just created. It is worth mentioning that you must prefix database schema before the user-defined function. If you look at the script we have specified the schema in the following line:

DOB is passed as parameter to the “getFormatted” function which returns the formatted date. The above query will retrieve following results:

There we have it; a simple user-defined function.

References:

• Comparison of Stored Procedures And Functions
• Understand When To Use User-Defined Functions In SQL Server
• A Full List Of Built-In Functions In SQL

Other great articles from Ben

• Author
• Recent Posts

Ben Richardson

Ben Richardson runs Acuity Training a leading provider of SQL training the UK. It offers a full range of SQL training from introductory courses through to advanced administration and data warehouse training – see here for more details. Acuity has offices in London and Guildford, Surrey. He also blogs occasionally on Acuity’s blog

View all posts by Ben Richardson

Latest posts by Ben Richardson (see all)

• Working with the SQL MIN function in SQL Server - May 12, 2022
• SQL percentage calculation examples in SQL Server - January 19, 2022
• Working with Power BI report themes - February 25, 2021