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:
- Scalar functions: The function that returns a single data value is called a scalar function.
- 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.
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.
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
RETURN @avgSal; returns a value stored in a variable
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:
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
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;
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
GetSeniorEmployees() function contains multiple statements within
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.