Tutorial Gateway

  • C
  • C#
  • Python
  • SQL
  • Java
  • JS
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Go Programs
    • Python Programs
    • Java Programs
  • MySQL

User Defined Functions in SQL

The User Defined Functions in SQL Server are like functions in any other programming language that accepts the parameters, performing complex calculations, and returning the result value.

Types of Functions in SQL Server

There are two types of functions in SQL Server:

  1. System Functions: All the built-in functions supported by the Server called System functions in SQL Server. We don’t have to bother about the logic inside them because they cannot be modified. For example, Mathematical Functions, Ranking Functions, String Functions, etc.
  2. User Defined Functions: SQL Server allows us to create our functions called as user defined functions in SQL Server. For example, if we want to perform some complex calculations, then we can place them in a separate function, and store it in the database. Whenever we need the calculation, we can call it. There are two types of SQL user defined functions:
    • Scalar Function: It is a function that returns a single value. Generally, we have to define the function body between BEGIN … END block, but for inline scalar function, you can omit them. We can use any SQL data type as the return type except text, image, ntext, cursor, and timestamp.
  • Table Valued Functions: It is a user defined function in SQL Server that returns a table.
    • Inline Table valued Functions: This function returns a table data type based on a single SELECT Statement

Advantages of User Defined Functions in SQL Server

  1. User defined functions in SQL Server prevent us from writing the same logic multiple times.
  2. Within the Database, you can create the function once, and call it n number of times.
  3. SQL user defined functions reduce the compilation time of query by catching the execution plan and reusing them.
  4. This user defined functions can help us to separate the complex calculations from the regular query so that we can understand and debug the query quicker and better.
  5. It reduces the network traffic because of its cache plan
  6. SQL Server Functions also used in WHERE Clause as well. By this, we can limit the number of rows sent to the client.

Syntax of User Defined Functions in SQL Server

The syntax the SQL Server User defined functions or UDF is

CREATE FUNCTION Function_Name(@Parameter_Name Data_type, 
                                 .... @Parameter_Name Data_type
                             )
RETURNS Data_Type
AS
   BEGIN
      -- Function Body
      
      RETURN Data 
   END
  • Return_Type:
    1. Data Type: Please specify the data type of return value. For example, VARCHAR, INT, FLOAT, etc.
    2. Data: Please specify the return value, and it should match the Data Type. It can be a single value or Table
  • Function_Name: You can specify any name you wish to give other than the system reserved keywords. Please try to use meaningful names so that you can identify them easily.
  • @Parameter_Name: Every function accepts zero or more parameters; it completely depends upon the user requirements. While declaring the parameters don’t forget the appropriate data type. For example (@name VARCHAR(50), @number INT)
  • Function Body: Any query, or any complex mathematical calculations you want to implement in this particular function.

Let us see how to create or write different types of User Defined Functions in SQL Server with example.

For this SQL UDF demonstration, We are going to use [MyEmployee table] and [Department] table present in our [SQL Tutorial] Database.

TIP: Please refer to Alter User Defined Functions in SQL Server article to understand, How to Rename, Modify, or Delete SQL Server UDFs.

From the below figure you can observe that [MyEmployee table] table have fourteen records

User Defined Functions in SQL Server 1

And [Department] table has eight records.

User Defined Functions in SQL Server 0

Create SQL Scalar Function example

The Scalar User defined functions in SQL Server are very useful when you want to return a single value as the resultant. For example, total sales, or total investments, total loss, or total expenditure etc.

Create SQL Scalar Function with No parameters example

In this simple SQL scalar function example, we will show you how to create the Scalar function without any parameters.

From the below query, you can observe that we are summing the Yearly Income of the MyEmployee table.

-- SQL User Defined Functions - SQL Scalar Function example
CREATE FUNCTION NoParameters ()
  RETURNS INT
  AS
    BEGIN 
       RETURN (SELECT SUM([YearlyIncome]) FROM [MyEmployees Table])
    END
User Defined Functions in SQL Server 2

Let me show you, How it looks in the SQL Management Studio

User Defined Functions in SQL Server 3

Let us see the Output of the SQL scalar function

SELECT [EmployeeID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,dbo.NoParameters() AS [Average Income]
      ,[Sales]
      ,[HireDate]
  FROM [MyEmployees Table]
User Defined Functions in SQL Server 4

Create SQL Scalar Function with parameters example

In this example we will show you, How to create SQL Scalar function with parameters. From the below query you can observe that, we are concating First name and Last Name.

NOTE: We are using the SPACE function to provide the space between the First name and last name.

-- SQL User Defined Functions - Scalar Functions example
CREATE FUNCTION fullName (@firstName VARCHAR(50), @lastName VARCHAR(50))
  RETURNS VARCHAR(200)
  AS
    BEGIN 
       RETURN (SELECT  @firstName + SPACE(2) + @lastName )
    END

Let us see the Output by using the below query

USE [SQL Tutorial]
GO
SELECT [EmployeeID]
       -- Passing Parameters to fullname Function
      ,dbo.fullname([FirstName], [lastName]) AS [Name]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [MyEmployees Table]
User Defined Functions in SQL Server 5

Create SQL Scalar Function Where Clause example

The following SQL scalar function where clause example will accept the varchar as the parameter. And it finds the sum of the Sales amount, whose Occupation is equal to the parameter that we pass.

-- SQL User Defined Functions - SQL Scalar Function example
CREATE FUNCTION average (@Occupation VARCHAR(50))
  RETURNS FLOAT
  AS
    BEGIN 
       RETURN (SELECT  SUM([Sales]) FROM [MyEmployees Table]
	        WHERE [Occupation] = @Occupation)
    END

Let us see the Output

USE [SQL Tutorial]
GO
SELECT [Occupation]
      ,SUM([YearlyIncome]) as [Total Income]
      ,SUM([Sales]) AS [Total Sale]
      ,dbo.average([Occupation]) AS [Total Sale from Function]
  FROM [MyEmployees Table]
  group by [Occupation]
User Defined Functions in SQL Server 6

Use SQL Scalar Function in Where Clause example

In this example, we show you how to use the SQL Scalar function in WHERE Clause

-- SQL User Defined Functions - SQL Scalar Function example
USE [SQL Tutorial]
GO
CREATE FUNCTION AverageSale ()
  RETURNS FLOAT
  AS
    BEGIN 
       RETURN (SELECT  AVG([Sales]) FROM [MyEmployees Table])
    END

Let us see the Output

USE [SQL Tutorial]
GO
SELECT [EmployeeID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [MyEmployees Table]
  WHERE [Sales] >= dbo.AverageSale()
User Defined Functions in SQL Server 7

Create SQL Server Inline Table valued Functions examples

The SQL Server Inline function returns a table data type as the return value based on a single SELECT Statement

Create SQL Inline Function with No parameters example

In this simple example we will show you, How to create a SQL Inline table valued function without any parameters. From the below query, you can observe that we are selecting the top 10 records from the MyEmployee table.

-- SQL User Defined Functions - Inline Functions example
USE [SQL Tutorial]
GO
CREATE FUNCTION TopTenCustomers ()
  RETURNS TABLE
  AS
       RETURN (
		   SELECT TOP 10 [FirstName]
		  ,[LastName]
		  ,[Education]
		  ,[Occupation]
		  ,[YearlyIncome]
		  ,[Sales]
		  ,[HireDate]
		  FROM [MyEmployees Table]
               )

Let us see the Output

USE [SQL Tutorial]
GO
SELECT * FROM [dbo].[TopTenCustomers] ()
GO
User Defined Functions in SQL Server 8

SQL Inline Function with Parameters Example

This SQL Server functions example shows how to create an SQL Inline table valued function with parameters.

From the below query, you can see we are selecting the records from both the table using INNER JOIN, whose Occupation is equal to a parameter that we pass.

-- SQL User Defined Functions - Inline Functions example
USE [SQL Tutorial]
GO
CREATE FUNCTION CustomerbyDepartment (@profession VARCHAR(50))
  RETURNS TABLE
  AS
     RETURN (
		SELECT  [FirstName]
                ,[LastName]
		,[Occupation]
		,[Education]
		,dept.DepartmentName AS Department
		,[YearlyIncome] AS Income
		,[Sales]
		FROM [MyEmployees Table]
		INNER JOIN 
		Department AS dept ON
		  Dept.[id] = [MyEmployees Table].DeptID
		WHERE [Occupation] = @profession
		)

Let us see the Output of the SQL Inline table valued function

USE [SQL Tutorial]
GO

SELECT * FROM [dbo].[CustomerbyDepartment] ('Management')
GO
User Defined Functions in SQL Server 9

Multi-select Table valued Functions in Sql Server examples

The SQL Server Multiple select table valued functions return the tabular result set. However, unlike the inline table valued function, we can use multiple select statements inside the function body.

In this SQL Server Multi select table valued function example will show you, How to use Multiple statements in one User defined function.

-- SQL User Defined Functions - Table Valued Functions example
USE [SQL Tutorial]
GO
CREATE FUNCTION CustomerDepartment()
  RETURNS @customers TABLE
  (
  	[EmployeeID] [smallint] NOT NULL,
	[FirstName] [nvarchar](30) NULL,
	[LastName] [nvarchar](40)  NULL,
	[Education] [nvarchar](255) NULL,
	[Occupation] [nvarchar](255) NULL,
	[YearlyIncome] [float] NULL,
	[Sales] [float] NULL,
	[HireDate] [date] NULL,
	[DepartmentName] [VARCHAR](50) NULL
  )
  AS BEGIN
   INSERT INTO @customers
   SELECT  [EmployeeID]
	  ,[FirstName]
          ,[LastName]
	  ,[Education]
	  ,[Occupation]
	  ,[YearlyIncome]
	  ,[Sales]
	  ,[HireDate]
	  ,dept.DepartmentName
	FROM [MyEmployees Table]
	INNER JOIN 
	     Department AS dept ON
		Dept.[id] = [MyEmployees Table].DeptID
    
   -- Updating the Records
   UPDATE @customers SET [YearlyIncome] = [YearlyIncome] + 35200
	WHERE [Sales] > (SELECT AVG(Sales) FROM [MyEmployees Table])
  RETURN
 END

ANALYSIS

Within this User Defined Functions example, First, we are creating a table variable called @customers using the following statement

@customers TABLE

Next, we are inserting the records from [MyEmployee Table], and [Department] table into @customers table variable.

INSERT INTO @customers
   SELECT

Next, we are updating the Yearly income of all the customers present in the @customers table variable, whose yearly income is greater than the average sales.

UPDATE @customers SET [YearlyIncome] = [YearlyIncome] + 35200
WHERE [Sales] > (SELECT AVG(Sales) FROM [MyEmployees Table])

Let us see the Output

USE [SQL Tutorial]
GO

SELECT * FROM [dbo].[CustomerDepartment] ()
GO
User Defined Functions in SQL Server 10

Multiple User Defined Functions in SQL Server

How to use multiple User defined functions in a single SELECT Statement.

USE [SQL Tutorial]
GO
SELECT [EmployeeID]
      ,dbo.fullName([FirstName], [LastName]) AS Name --  First UDF
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [MyEmployees Table]
  WHERE [Sales] >= dbo.AverageSale() -- Second UDF
User Defined Functions in SQL Server 11

One UDF inside another UDF in SQL Server

In this simple Sql server UDF example, we will show you, How to nest or insert one User defined function inside another User defined function.

USE [SQL Tutorial]
GO
CREATE FUNCTION CustDepartment (@education VARCHAR(50))
  RETURNS TABLE
  AS
   RETURN (
	SELECT  dbo.fullName([FirstName],[LastName]) AS NAME
	,[Occupation]
	,[Education]
	,dept.DepartmentName AS Department
	,[YearlyIncome] AS Income
	,[Sales]
	FROM [MyEmployees Table]
	INNER JOIN 
	Department AS dept ON
	  Dept.[id] = [MyEmployees Table].DeptID
	WHERE [Education] = @education
)

Let us see the Output of the Nested user defined functions in Sql Server

USE [SQL Tutorial]
GO

SELECT * FROM [dbo].[CustDepartment] ('Bachelors')
User Defined Functions in SQL Server 12

Limitations of User Defined Functions in SQL Server

The following are the list of limitations of the SQL Server User defined Functions

  1. We cannot use the User defined functions, shortly called as UDF in SQL Server to modify the database state.
  2. SQL UDF can not return multiple result sets.
  3. The SQL UDF does not support error handling, such as TRY..CACHE, RAISEERROR, or @ERROR
  4. We cannot call a Stored Procedure from SQL UDF, but we can call an extended Stored Procedure
  5. The SQL User Defined Functions do not support the temporary tables, but it will allow the Table variable.
  6. SET statements are not allowed in SQL UDFs
  7. The FOR XML Clause is not permitted inside the functions

Filed Under: SQL

  • Install SQL Server
  • Install SQL Management Studio
  • Uninstall Management Studio
  • Install AdventureWorks Database
  • SQL Management Studio Intro
  • Connect SQL with sqlcmd utility
  • SQL Attach Database
  • SQL Detach Database
  • SQL Restore Database
  • Restore Database using BAK
  • SQL Rename Database with Files
  • Get SQL Database Names
  • SQL Create Table
  • SQL Rename Table
  • SQL Alter Table
  • SQL Add Column
  • SQL Rename Column
  • Get SQL Table Names in a DB
  • Find SQL Table Dependencies
  • Rename SQL Table & Column
  • SQL Global & Local Temp Table
  • SQL Table Variable
  • SQL Derived Table
  • SQL DATALENGTH
  • SQL Data Types
  • DML, DDL, DCL & TCL Cmds
  • SQL Query Builder
  • SQL ALIAS
  • SQL SELECT Statement
  • SQL SELECT DISTINCT
  • SQL SELECT INTO Statement
  • SQL INSERT Statement
  • SQL INSERT INTO SELECT
  • SQL BULK INSERT or BCP
  • SQL UPDATE Statement
  • SQL UPDATE from SELECT
  • SQL DELETE Statement
  • SQL TRUNCATE Table
  • SQL CASE Statement
  • SQL MERGE Statement
  • SQL Subquery
  • SQL CTE
  • SQL PIVOT
  • SQL UNPIVOT
  • SQL Clauses Examples
  • SQL TOP Clause
  • SQL WHERE Clause
  • SQL ORDER BY Clause
  • SQL GROUP BY Clause
  • SQL HAVING Clause
  • SQL Primary Key
  • SQL Foreign Key
  • SQL Referential Integrity
  • SQL Check Constraint
  • SQL Unique Constraint
  • SQL Default Constraint
  • SQL Clustered Index
  • SQL Non Clustered Index
  • SQL Filtered Indexes
  • SQL COALESCE Function
  • SQL IS NOT NULL
  • SQL IS NULL Function
  • SQL ISNULL
  • SQL JOINS
  • SQL CROSS JOIN
  • SQL FULL JOIN
  • SQL SELF JOIN
  • SQL Outer Joins
  • SQL Cross Join Vs Inner Join
  • SQL LEFT JOIN
  • SQL RIGHT JOIN
  • SQL AND & OR Operators
  • SQL Arithmetic Operators
  • SQL BETWEEN Operator
  • SQL Comparison Operators
  • SQL LIKE
  • SQL EXCEPT
  • SQL EXISTS Operator
  • SQL NOT EXISTS Operator
  • SQL INTERSECT
  • SQL IN Operator
  • SQL NOT IN Operator
  • SQL UNION
  • SQL UNION ALL
  • SQL IF ELSE
  • SQL ELSE IF
  • SQL WHILE LOOP
  • SQL Nested While Loop
  • SQL BREAK Statement
  • SQL CONTINUE Statement
  • SQL GOTO Statement
  • SQL IIF Function
  • SQL CHOOSE Function
  • SQL Change Data Capture
  • SQL Table Partitioning
  • SQL Table Partition using SSMS
  • SQL TRY CATCH
  • SQL VIEWS
  • SQL User Defined Functions
  • SQL Alter User Defined Functions
  • SQL Stored Procedure Intro
  • Useful System Stored Procedures
  • SQL SELECT Stored Procedure
  • SQL INSERT Stored Procedure
  • SQL UPDATE Stored Procedure
  • Stored Procedure Return Values
  • Stored Procedure Output Params
  • Stored Procedure Input Params
  • Insert SP result into Temp Table
  • SQL Triggers Introduction
  • SQL AFTER INSERT Triggers
  • SQL AFTER UPDATE Triggers
  • SQL AFTER DELETE Triggers
  • SQL INSTEAD OF INSERT
  • SQL INSTEAD OF UPDATE
  • SQL INSTEAD OF DELETE
  • SQL STATIC CURSOR
  • SQL DYNAMIC CURSOR
  • SQL FORWARD_ONLY Cursor
  • SQL FAST_FORWARD CURSOR
  • SQL KEYSET CURSOR
  • SQL TRANSACTIONS
  • SQL Nested Transactions
  • SQL ACID Properties
  • Create SQL Windows Login
  • Create SQL Server Login
  • SQL Server Login Error
  • Create SQL Server Roles
  • SQL Maintenance Plan
  • Backup SQL Database
  • SQL Ranking Functions Intro
  • SQL RANK Function
  • SQL PERCENT_RANK Function
  • SQL DENSE_RANK Function
  • SQL NTILE Function
  • SQL ROW_NUMBER
  • SQL Aggregate Functions
  • SQL Date Functions
  • SQL Mathematical Functions
  • SQL String Functions
  • SQL CAST Function
  • SQL TRY CAST
  • SQL CONVERT
  • SQL TRY CONVERT
  • SQL PARSE Function
  • SQL TRY_PARSE Function
  • SQL Calculate Running Total
  • SQL Find Nth Highest Salary
  • SQL Reverse String
  • SQL FOR XML PATH
  • SQL FOR XML AUTO
  • SQL FOR XML RAW

Copyright © 2021· All Rights Reserved by Suresh.
About | Contact | Privacy Policy