User Defined Function in SQL

User Defined Function

User Defined Function. After learning the Stored Procedure, it’s time to talk about user-defined functions. When we discuss the User-defined function, you will imagine it is like store procedures but it is similar not exactly like Stored Procedures. The UDF takes in the parameters and does some calculations or performs some action then gives out a single value or a result set.

The user-defined functions are also called Inline. If you are aware of any programming language where you have already created methods or functions, you will appreciate the concept of UDF.

There are three types of User-Defined Functions

Scalar Function – The user-defined scalar function returns a single data value of the type defined in the return clause.

Table-Valued Function – The user-defined table-valued functions return a table data type.

System Function – SQL server provides many system functions that you can use to perform a variety of operations. But we can not modify system functions. For example, you will have the getdate function in SQL. You can use this function to get the system date but it cannot be modified.

Creating a User-Defined Function

Just like our previous articles, we will be using Adventureworks for this demonstration. The table that we will use in this demonstration is sales.salesTerritory. First, we will try with a Scalar function and then move to the next one. We will be doing it in steps.

Step 1

First of all, select the right database by executing the following query. By default when you open SQL Server Management Studio, you will have the master database selected.

use AdventureWorks2019

Step 2

See the table that you are going to work on.

Select * from [Sales].[SalesTerritory]

Step 3

Below is the query that you need to run for creating User Defined Function (UDF).

CREATE FUNCTION YTDSALES()
Returns Money
As
Begin
Declare @YTDSALES Money
Select  @YTDSALES = SUM(SalesYTD) from [Sales].[SalesTerritory]
Return @YTDSALES
End

You can see the query starts with the Create Function keyword and followed by the function name that is YTDSALES(). In the next like the return type of this function is mentioned that is Money. The As Begin tells the SSMS that now the functionality of the UDF needs to be defined.

In the function first we are declaring a variable named YTDSALES and setting its return type as Money.

In the next like We are selecting one column from the table ([Sales].[SalesTerritory]) but not just one value but the entire column. We using the SUM function to do the sum of all the values in the column. The SUM function is a system function. The SUM of the values we are saving in the variable @YTDSALES.

In next line we are returning the value of the variable. End keyword is there to tell the system that the functionality code ends here. The functionality code starts just after Begin Keyword and ends before the End keyword.

Step 4

We know the User defined function that we just created returns some value as Money.

Declare @YTDVariable As Money
Select @YTDVariable = dbo.YTDSALES()
Print @YTDVariable

So below we have declared a variable by using the declare keyword and then saved the result of the User-defined function (YTDSALES()) in that variable. In the last, we have printed the value of the variable by using the print keyword.