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

We have learned how to use many of the built-in system functions that are available in the SQL Server in the previous article, Now in this post will turn our attention 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

  1. Scalar functions
  2. Inline table-valued functions
  3. Multi-statement table-valued functions.

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

Now before in looking at how to create a scalar function, let’s look at how to use one of the existing system functions.

For example, we have SQUARE() function which is a scalar in-built system, so let’s try to use that. And to use that, we used to select a keyword and then SQUARE the name of the function itself.

select SQUARE(2)

when I hover the mouse on this function, look at the intelligence of SQL SERVER management studio, it shows that this function is expecting a float parameter and it returns a float data type, a float value.

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

select SQUARE(2)

if you understand the functions, have the capability of taking in parameters, do some processing and return some values back.

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

For example, if you take the GETDATE() method, which returns the current system daytime, it doesn’t take any parameter. Look at this. This function doesn’t expect any parameter

select GETDATE()

getdate function

so a function can have parameters but a function should always return a value. so now let’s go in and see how to create a user-defined scalar function.

So 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 scalar function.

To create a scalar function we use the following syntax –Create function function_name. Similarly to create a stored procedure we will use create procedure procedure_name.
Similarly to create a table we use create table table_name to create a function,create function function_name.

And 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 2024 parameters, and then a function will also return in value.So to specify what is the function going to return, we use the returns keyword and then 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 that we have except text and ntext image cursor and timestamp. A function cannot return these data types.

AS BEGIN just like the stored procedure, the body of the function goes inside this begin and end statements.

So let’s see how to create a user-defined function that computes the age of the 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 function, I’ve created a variable called at @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 date parameter and it should return back an integer, which is nothing but age of the person AS BEGIN and finally END.

Whoever calls this function, returns the age back to that calling program.so if you look at the syntax of the function, create function function_name parameters,returns, data type AS BEGIN END, and within the BEGIN, then 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 if we go to the database programmability and functions and then scalar function. why it is called scaler function because it returns a single value.

scaler function

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

That’s nothing, but DBO stands for database owner and then .GetAge is the name of the function and 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 involve this function, just like how we have invoked getdate function.We do it the same way, but we need to use a two-part name. Let’s understand that.

Now, look at this. I’m just saying select GetAge. And 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 database owner. 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 over 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 date of birth.
tblusers
So based on the date of birth, we can actually calculate the age of the user.we have written a scaler of function for that. So what we are going to do here?we want to name and the date of birth and to get that, we are using the age function.

This function is going to calculate an 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. A simple one difference between a function and a stored procedure.

Deterministic and non-deterministic functions

Now, what are deterministic and non-deterministic functions, and what are the differences between them? This is a very commonly asked interview question as well.

Now let’s understand these with an example. For example, we have a function called Square() Function which will square the number that we provide to it.

select SQUARE(2)

For example, if I pass in 2 and I execute this query, look at this. Every time you execute this, it gives you 4, Every time you execute that, it gives you 4 so you can determine the result of this function. So let’s look at the definition of the deterministic function.

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

so here we have not changed the input value. So when we execute this function, I get the same result no matter how many times they execute that.

Non-deterministic functions

Obviously non-deterministic functions are opposite to deterministic functions for deterministic functions. They always return the same result for the given state and for the given set of input values, whereas non-deterministic functions may return different values for the same set of specific input values. And even if the database state has not changed.

The classic example is to Getdate() function.Every time you execute that the time is going to change for the same set of input values.

This function doesn’t take any input parameters and, and even if the database state has not changed, I get a different result every time I execute this function so that’s an example of a non-deterministic function.

The post Scalar User-defined functions In sql server-Step by Step appeared first on Software Development | Programming Tutorials.



Read More Articles