Scalar User-defined functions In Sql Server-Step by Step

In this post, we will learn about user-defined functions and their types, including creating and calling a scalar user-defined function, identifying places where we can use these functions, and finally, how to alter and drop a user-defined function.

We have already covered how to use many of the built-in system functions available in SQL Server in the previous article. Now, in this post, we will shift our focus to creating user-defined functions.

Types of user-defined functions in sql server

User-define functions are also called UDS. In the sql server, There are three types of user-defined functions

In this post, we will concentrate on how to create scalar functions.

Before delving into creating a scalar function, let’s explore how to use one of the existing system functions.

For example, we have the SQUARE() function, which is a scalar built-in system function. To use it, we typically use the SELECT keyword followed by SQUARE and the name of the function itself.

select SQUARE(2)

When I hover the mouse over this function, observe the intelligence of SQL Server Management Studio. It indicates that this function expects a float parameter and returns a float data type, which means it returns a float value.

SQUARE
So if we provide it with a number, for example, let’s say we pass in 2, this function is going to square that number and return the square back to us in the form of a float. When we execute this, we get 4.

select SQUARE(2)

If you understand, functions have the capability of taking in parameters, performing some processing, and returning values.

Now, is it mandatory for a function to have a parameter? No, we have also looked at many functions that don’t take parameters at all.

For example, if we consider the GETDATE() function, which returns the current system datetime, it doesn’t take any parameters. Look at this. This function doesn’t expect any parameters.

select GETDATE()

getdate function

So, a function can have parameters, but a function should always return a value. Now, let’s proceed to see how to create a user-defined scalar function.

What is a scalar function?

A scalar function is a function that takes zero or more parameters and returns a value. A function may or may not have parameters, but it should return a value. Since a scalar function returns a single scalar value, it’s called a scalar function.

To create a scalar function, we use the following syntax: CREATE FUNCTION function_name. Similarly, to create a stored procedure, we use CREATE PROCEDURE procedure_name. Just like creating a table with CREATE TABLE table_name, to create a function, we use CREATE FUNCTION function_name.

Then, we know that a function can have parameters. So, within the parentheses, you specify the name of the parameter and its data type.

CREATE FUNCTION Your_Function_Name(@InputParameter1 DataType, @InputParameter2 DataType,..@InputParametern Datatype)
RETURNS Return_Datatype
AS
BEGIN
Function Body
Return Return_Datatype
END

You can have up to 2024 parameters, and then a function will also return a value. To specify what the function is going to return, we use the RETURNS keyword followed by the data type.

Now, remember, a scalar function can return almost any of the data types that we have, any of the scalar data types except text, ntext, image, cursor, and timestamp. A function cannot return these data types.

AS BEGIN, just like in a stored procedure, the body of the function goes inside these BEGIN and END statements.

So let’s see how to create a user-defined function that computes the age of a person.

CREATE FUNCTION GetAge(@UserDOB Date)
RETURNS INT
AS
BEGIN
DECLARE @User_Age INT
SET @User_Age = DATEDIFF(YEAR, @UserDOB, GETDATE()) - CASE WHEN (MONTH(@UserDOB) > MONTH(GETDATE())) OR (MONTH(@UserDOB) = MONTH(GETDATE()) AND DAY(@UserDOB) > DAY(GETDATE())) THEN 1 ELSE 0 END
RETURN @User_Age
END

If you look at the function, I’ve created a variable called @UserDOB of type Date, and there is another variable of type integer, @User_Age, which is used to hold the age of the user.

This function requires a date of birth as the input parameter. So let’s say @UserDOB, and this has to be a date parameter. It should return an integer, which is the age of the person.

AS BEGIN and finally END. Whoever calls this function, it returns the age back to that calling program.

So if you look at the syntax of the function, it's like this: CREATE FUNCTION function_name (parameters) RETURNS data_type AS BEGIN END. Within the BEGIN, the function body goes in, and finally, you should have a return statement.

So when we execute this, what happens? This function gets created in our database.

If I expand the database, and go to the database programmability and functions, then scalar function. It's called a scalar function because it returns a single value.

scaler function

And if I expand scalar functions here, scalar-valued functions, and then refresh, you should see the GetAge function that we have written. Look at the name, it says dbo.GetAge.

That’s nothing but 'dbo' stands for the database owner, and then '.GetAge' is the name of the function. This function is actually present in the ‘DemoDataBase’ database. So the fully qualified name of this function is DemoDataBase.dbo.GetAge.

How to execute function in sql with parameters

To use this function, similar to how we invoke the `getdate` function, we do it in the same manner, but we need to use a two-part name. Let’s understand that.

Now, take a look at this. I’m simply using `select GetAge`. When I execute this, it says 'GetAge' is not recognized as a built-in function.

Msg 195, Level 15, State 10, Line 10
‘GetAge’ is not a recognized built-in function name.

excute scalar function

And that’s because whenever you invoke a scalar user-defined function, you need to specify at least the two-part name: the database owner and the name of the function. Here, the database owner is DBO.

select dbo.GetAge('01/08/1994')

Now you can also use the fully qualified name,

Select DemoDataBase.dbo.GetAge('10/08/1982')

How do you use user-defined function?

You know, we have used it in the select statement. We have this table TblUsers. Now, I want you to write a query that will give me the name, date of birth, and the age of the users. And obviously, we have a function. And look at this in the database. We are not storing the age of the user, but we have the date of birth.

So based on the date of birth, we can actually calculate the age of the user. We have written a scalar function for that. So what are we going to do here? We want the name and the date of birth, and to get that, we are using the Age function.

This function is going to calculate the age for every person and return that. So you can use this user-defined scalar function in the select query, not only in the select clause. You can also use it in the where clause.

Select Name, DOB, dbo.GetAge(DOB) as UserAge from TblUsers

Now I want all the users in all the TblUsers whose age is greater than 18

Select Name, DOB, dbo.GetAge(DOB) as UserAge from TblUsers
Where dbo.GetAge(DOB) > 18

scalar function result

Now, whatever we have achieved using this scalar `dbo.GetAge` function, we can also achieve it using stored procedures. We can write a stored procedure for that also. If you want, you can quickly convert this function into a stored procedure.

We have just seen that a function can be used in the select and where clause, but can I use a stored procedure in the select and where clause? You cannot do that. If you try to do that, you will get an error. This is a simple difference between a function and a stored procedure.

Deterministic and non-deterministic functions

Now, let's understand deterministic and non-deterministic functions and the differences between them. This is a commonly asked interview question as well.

Let's illustrate this with an example. Consider a function called `Square()` function, which squares the number provided to it.

select SQUARE(2)

For example, if I pass in 2 and execute this query, notice that it consistently returns 4 every time it's executed. So, you can reliably determine the result of this function. Let's define deterministic functions.

A deterministic function always returns the same result when called with a specific set of input values and given the same state of the database.

In this case, we haven't changed the input value. Therefore, each time we execute this function, we get the same result.

Non-deterministic functions

Non-deterministic functions, in contrast to deterministic functions, behave differently. While deterministic functions consistently return the same result for a given set of input values and database state, non-deterministic functions may return varying values for the same input values, even if the database state remains unchanged.

A classic example of a non-deterministic function is the GetDate() function. Each time it's executed, the current time is retrieved, resulting in a different output even if the input parameters and the database state remain constant.