Scalar User-defined functions In sql server-Step by Step

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.

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.

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()

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.