What is Function in Sql Server [Functions]

SQL Server Functions

Function is set of statements or set of queries that take input parameter, process it and return the result. Function must return a value that can be a single or table. we can use  sql function with DML command like insert , delete, update. 

Types of function

1:- System Defined Function

2:- User Defined Function

System Defined Function

There is two type of system defined function that is defined by Sql Server for different purpose. We can use these function as per our requirements.

1:- Scalar Function

Scalar functions works on a single value and returns a single value. Following are the most useful scalar function in SQL server and it returns a single value

• UPPER () - Converts a input string to upper case
• LOWER () - Converts a input string to lower case
• SUBSTRING () - Extract characters from a text field
• LEN () - Returns the length of a input value
• ROUND () - Rounds a numeric value to the number of decimals specified
• GETDATE () - Returns the current system date and time
• FORMAT() - Formats how a field is to be displayed

For more detail about Sql scalar function, click here

2:- Aggregate Function

Aggregate functions work on a collection of values and return a single value. Following are the most used Sql aggregate functions.

• MAX() - Returns the largest value of the selected column
• MIN() - Returns the smallest value of the selected column.
• AVG() - Returns the average value of a numeric column.
• SUM() - Returns the total sum of a numeric column.
• COUNT() - Returns the number of rows that matches a specified criteria.

For more detail about Sql Aggregate function, click here

User Defined Function

User defined function is created by user in database as per their requirements. There are 3 types of user defined functions.

• ·         User Defined Scalar Function
• ·         Inline Table-Valued Function
• ·         Multi-Statement Table-Valued Function

1:- User Defined Scalar Function

This function also returns single value as a result. We return any data type value from user defined scalar function.

Example:-

Create FUNCTION [dbo].[enumAbsentReason]
(
       @EnumId INT
)
RETURNS VARCHAR(100)
AS
BEGIN
       -- Declare the return variable here
       DECLARE @result VARCHAR(100)
 
       SET @result = (SELECT
       CASE WHEN @EnumId = 1 THEN 'Hospitalization'
               WHEN @EnumId = 2 THEN 'Contractual Absence'
               WHEN @EnumId = 3 THEN 'Absent'
               WHEN @EnumId = 4 THEN 'Other'
               WHEN @EnumId = 5 THEN 'AWOL'
               WHEN @EnumId = 6 THEN 'Detention'
               WHEN @EnumId = 7 THEN 'Home Visit'                                   
               ELSE
               ''
       END)
              
       RETURN @result
END

Calling above Function

DECLARE @EnumId int=1;

Select dbo.enumAbsentReason(@EnumId)

Output:-  Hospitalization

2:- Inline Table-Valued Function

Inline Table-Valued Function returns the table value as result after performing certain process. it is stored in Inline Table-Valued Function in database. it should be done by using SELECT statements

Example:-

CREATE FUNCTION GetEmployeelist()
RETURNS TABLE
AS
RETURN
(
       -- Add the SELECT statement with parameter references here
       SELECT * from Employee where Department=
)
GO

Calling Inline Table-Valued Function

SELECT * FROM dbo.GetEmployeelist()

Output:-

Id  Name    Age Address Department  Gender
1   Alok Kumar Singh    30  IN  IT  Male
2   Tomas Paul  45  NZ  IT  Male
3   Cristomfer Dee  85  AU  HR  Female
4   Niel Macengi    55  US  BPO Female
5   Rahul Singh 25  IN  IT  Male
6   Manoj Singh 12  IN  IT  Male
7   Adam Zempa  58  IN  BPO Male
8   Crisno Crief    96  AU  HR  Female
9   David Martin    56  AU  IT  Male
10  Ricky Ponting   21  US  BPO Male
11  Poing Cahndda   66  IN  IT  Male
12  Chandan Subhash 46  IN  HR  Male
13  M. Shekh    45  NZ  BPO Female

3:-Multi-Statement Table-Valued Function

This function returns the table value as result after performing certain process.  In Multi-Statement Table-Valued Function, you are allowed to use table variables  as per your requirement and then this table variables  can return as result set.

Example:-

Create FUNCTION [dbo].[GetEmployeebyDepartment]
(
    @Department varchar(50)=null
    
)
RETURNS 
@tblEmployeeInfo TABLE 
(
    EmployeeId INT,
    Name VARCHAR(255),
    Department varchar(50),
    Address varchar(50)
    
)
AS
BEGIN 
INSERT INTO @tblEmployeeInfo
        SELECT id,Name,Department,Address from Employee where Department=@Department 
RETURN  
END

Calling Inline Table-Valued Function

SELECT * FROM GetEmployeebyDepartment('IT')

Output:-

EmployeeId  Name    Department  Address
1   Alok Kumar Singh    IT  IN
2   Tomas Paul  IT  NZ
5   Rahul Singh IT  IN
6   Manoj Singh IT  IN
9   David Martin    IT  AU
11  Poing Cahndda   IT  IN

Limitation of User Defined Function

• Can not Error Handling: RAISERROR statement cannot be used within a User Definded Function. You can not even check the value of the @@ERROR global variable within a function
• Cannot Use Temporary Tables: User Definded Function can not make use of temporary tables. You are allowed to use table variables within a User Definded Function.
• Cannot Execute Dynamic SQL: User Definded Function also cannot execute dynamically SQL statements. If you want to create a statement dynamically based on the parameter values, you should use stored procedures.
• Cannot Call Stored Procedures: Stored procedures can not be call within User Definded Function.
• Returns Only One Result Set: User Definded Function can return only one rowset to the user, whereas stored procedures can return multiple rowsets
• Limitation of Using Cursors: User Definded Function cannot return data to the user through the FETCH keyword within a cursor.
• Smaller Number of Parameters:- User Definded Function can have up to 1024 parameters, whereas stored procedures support up to 2100 parameters
• No Use of Non-deterministic Built-in Functions: User Definded Function cannot use non-deterministic built-in functions. This rule prohibits usage of very common functions such as GETDATE() or RAND() in UDFs.