Introduction

Functions in SQL server are database objects that contains a set of sql statements which are commonly used and reused throughout the database to perform a specific task. Instead of writing the same code multiple times, we can define a function and call it whenever we need to perform a specific task. It makes the code more efficient and easier to maintain. In this blog, we are going to see functions and its types in sql.

Types of function

Generally, SQL functions are categorized into two types.

1)System functions

2)User defined function

System functions

Functions that are defined by the sql server system are known as system funcions. Sql server provides a wide range of functions to work with database objects and data.

Some of the system defined functions are

• Count()
• Sum()
• Min()
• Max()
• Avg()
• Len()
• Ltrim() & Rtrim()
• Upper() & lower()

User defined functions

Functions that are defined by the user are known as user defined functions. Based on the return types of functions, user defined functions are classified into two types.

1. Scalar function
2. Table valued function

Scalar function

Scalar functions accepts zero or more parameters as input and returns a single value.

Syntax:

Create function <function-name> (parameter 1, parameter 2,..., parameter n) 
Returns <return-type>
Begin
     Statement 1;
     Statement 2 ;
     .
     .
     .
     Statement n;
Returns <return-value>
End

Example:

S.No	Roll No	Student Name	Subject 1	Subject 2	Subject 3	Subject 4
1	1	Abarnaa	90	82	95	78
2	2	Gayathri	80	64	80	93
3	3	John	95	90	85	98
4	4	Ram	60	50	78	66
5	5	Sam	73	89	90	65

Consider the above table that contains student mark details. Lets create a function that returns the student total mark by passing the student roll no.

Create function GetStudentTotal(@RollNo varchar(10))
Returns decimal(18,2)
Begin
    Return (Select Subject1+Subject2+Subject3+Subject4 from student where rollNo=@RollNo)
End

Call the function by passing student roll no and get the total marks obtained by the student.

Select dbo.GetStudentTotal('1');

Table Valued function

Table valued function accepts zero or more parameters as input and returns a table variable as output.

Syntax:

CREATE function <function-name> (parameter 1, parameter 2,..., parameter n) 
RETURNS table  
AS  
RETURN <select query>

Consider the student mark details table. Lets create a table valued function that accepts roll no as input parameter and returns the student details that matches that roll no as output.

Create function GetStudentDetails(@RollNo varchar(10)) 
Returns table 
as 
    Return select * from Student where rollNo=@RollNo 

This function is called by the statement.

Select * from dbo.GetStudentDetails('1')

Results:

S.No	Roll No	Student Name	Subject 1	Subject 2	Subject 3	Subject 4
1	1	Abarnaa	90	82	95	78

Conclusion:

Hope this blog gave a clear understanding about various types of function available in sql and how to create and use those functions.