SQL Server - Functions

Functions in SQL Server are similar to functions in other programming languages. Functions in SQL Server contains SQL statements that perform some specific tasks. Functions can have input parameters and must return a single value or multiple records.

If your scripts use the same set of SQL statements repeatedly then this can be converted into a function in the database.

Types of Functions

SQL Server Functions are of two types:

System Functions: These are built-in functions available in every database. Some common types are Aggregate functions, Analytic functions, Ranking functions, Rowset functions, Scalar functions.

User Defined Functions (UDFs): Functions created by the database user are called User-defined functions. UDFs are of two types:

1. Scalar functions: The function that returns a single data value is called a scalar function.
2. Table-valued functions: The function that returns multiple records as a table data type is called a Table-valued function. It can be a result set of a single select statement.

The following is the simplified syntax of the user-defined function in SQL Server.

CREATE OR ALTER FUNCTION [schema_name.]function_name(@parameter_name parameter_data_type,...)
RETURNS <data_type>
    [WITH <function_options>]
    AS 
        BEGIN

            <function_body>
        
            RETURN <value or select_statement>
        END

Visit User-defined functions syntax for more information.

Scalar Functions

The scalar function always returns a single value. The following scalar function GetAvgSalary returns the average salary of the specified department.

CREATE or ALTER FUNCTION GetAvgSalary(@DeptID int)  
RETURNS float   --returns float type value
    AS 
    BEGIN
        
        DECLARE @avgSal float = 0; --declares float variable 
    
        -- retrieves average salary and assign it to a variable 
        SELECT @avgSal =  AVG(Salary) FROM Employee 
        WHERE DepartmentID = @DeptID   
    
        RETURN @avgSal; --returns a value
    END

In the above example, CREATE or ALTER FUNCTION indicates to create a new function or alter a function if exists. The GetAvgSalary is a function name, @DeptID is an input parameter of int type, RETURNS float specifies the float type value will be returned from a function. The function body starts with BEIGN and ends with END. The RETURN @avgSal; returns a value stored in a variable avgSal.

Note: Error handling is restricted in UDFs. UDF does not support TRY-CATCH, @ERROR, and RAISERROR.

A scalar function can be called in the SELECT clause, as shown below:

SELECT dbo.GetAvgSalary(5); 

You can also call a function from a stored procedure, as shown below.

CREATE PROCEDURE dbo.uspCallUserFunction(@DeptID int)  
    AS  
    BEGIN
        SELECT dbo.GetAvgSalary(@DeptID)
    END

Table-valued Functions

The table-valued function returns one or more records as a table data type.

The following Table-valued function returns all the rows from the Employee table where the HireDate is greater than the passed input parameter.

CREATE or ALTER FUNCTION dbo.GetEmployeeList(@hiredate date)
RETURNS TABLE
    AS
    RETURN
        SELECT * FROM Employee
        WHERE HireDate > @hiredate;

The above GetEmployeeList() function is inline table-valued function because it contains a single statement.

The following example executes a table-valued function to return a list of employees hired after 01/01/2010.

SELECT * FROM dbo.GetEmployeeList('01/01/2010')

The multi-statement table-valued function can contain multiple statements, as shown below.

CREATE or ALTER FUNCTION dbo.GetSeniorEmployees()
RETURNS @SrEmp Table
(
    EmpID int, 
    FirstName varchar(50)
)
AS
BEGIN
 Insert into @SrEmp Select EmployeeID, FirstName from Employee;

 delete from @SrEmp where EmpID > 10; --delete other employees
return
end 

The above GetSeniorEmployees() function contains multiple statements within BEGIN and END. It declares a table @SrEmp, inserts all employees into it, and then deletes some employees. Thus, you can include multiple statements in a table-valued function.

You can view the newly created user-defined functions in Object Explorer under Functions folder.

Create User-Defined Functions Using SSMS

Step 1: Open SQL Server Management Studio and connect to the database.

Step 2: Expand the database where you want to create a function. Expand Programmability.

Step 3: Right-click on Functions and select New. You get 3 options –

• Inline Table-valued Function
• Multi-Statement Table-valued Function
• Scalar-Valued Function

Step 4: Click on an option suitable for your new function based on the return value. This will open a template to create a function in a Query Window, as shown below.

Write appropriate statements and execute the script to create a function. Refresh the database in the object explorer to see the created functions under the Functions folder.

Delete User-Defined Functions

Use the DROP statement to delete a function, as shown below.

DROP FUNCTION dbo.GetEmployeeList;

Advantage of User-defined Functions

• Faster Execution: Similar to stored procedures, UDFs reduce the compilation cost of T-SQL by caching the plans and reusing them for future executions.
• Reduce Network Traffic: The SQL statements of a function execute in the database, and the application calling it needs to make a function call to the database.
• Supports Modular Programming: UDFs can be modified independently of the application source code. You can create UDFs once and store them in the database, and they can be called any number of times.

Learn what is the difference between Functions and Stored Procedure in SQL Server.