SQL User Defined Functions | What are SQL user Defined Functions

In this article, you will learn about SQL User Defined Functions including scalar-valued functions that return a single value and table-valued function which return rows of data. therefore Like SQL functions in programming languages, SQL Server user-defined functions are routines that accept the parameters and perform an action, like as a complex calculation, and return the result of that action as a value. therefore, the returns value can either be a single scalar value or a result set.
The SQL User-Defined functions help you simplify your development by encapsulating complex business logic and make them available for reuse in every query.

Why use SQL User-Defined Functions

Therefore, The main benefit of UDF (User-Defined Functions) is that we are not just limited to SQL provided functions. furthermore, We can write our own functions to meet our specific needs or to simplify complex SQL queries.

Note:
In the simplest way, a SQL user-defined function (UDF) in SQL Server is a programming construct that accepts parameters, does work that typically makes use of the accepted parameters, and returns a type of result.

Types of Functions in SQL Server

There are the following two types of SQL Server functions:
1. System Functions: All the built-in functions supported by the Server called System functions in SQL Server. therefore, We don’t have to bother about the logic inside them because they cannot be modified. For example, String Functions, Ranking Functions, Mathematical Functions, etc.

2. User-Defined Functions: The SQL Server allows us to create our functions called as user-defined functions in SQL Server. let us suppose example, if we want to perform some complex calculations, then we can place them in a separate function, and store it in the database. furthermore, Whenever we need the calculation, then we can call it. There are two types of SQL user-defined functions:

I) Scalar Function: It is a function that returns a single value. Normally, we have to define the function body between BEGIN … END block, but for inline scalar function, you can omit them. furthermore, We can use any SQL data type as the return type except text, image, cursor, and timestamp.
II) Table-Valued Functions: In SQL Server, It is a user-defined function that returns a table.
- a) Inline Table-valued Functions: This function in SQL Server that returns a table data type based on a single SELECT Statement

Syntax of User-Defined Functions in SQL Server

Therefore, the syntax User-defined functions in SQL Server 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

CREATE FUNCTION Function_Name(@Parameter_Name Data_type,

.... @Parameter_Name Data_type

)

RETURNS Data_Type

BEGIN

-- Function Body

RETURN Data

END

Return_Type:
- Data: specify the return value, and it should match the Data Type. furthermore, It can be a single value or Table
- Data Type: To specify the data type of return value. For example, INT, FLOAT, VARCHAR, etc.
Function_Name: To specify any name, which you want to give other than the system reserved keywords. therefore, use meaningful names so that you can identify them easily.
@Parameter_Name: Every function accepts zero or more parameters, furthermore, it completely depends upon the user requirements. therefore, While declaring the parameters, you don’t forget the appropriate data type. such as @number INT, @name VARCHAR(60)
Function Body: any complex mathematical calculations or any query you want to implement in this particular function.

How to Create Different Types of SQL User-Defined Functions

Let us Create user-defined functions in SQL Server with example.
Therefore, these SQL User Defined Function (UDF) example, We are going to use the TeacherDetails table and Faculty table which records are present in the as given in the demo table

Demo Table: TeacherDetails

CREATE TABLE TeacherDetails (
      TeacherID INT NOT NULL,
      TeacherName VARCHAR(60),
      FatherName VARCHAR(60),
	  Education VARCHAR(60),
	  Position VARCHAR(60),
	  TotalSalary INT NOT NULL,
	  AdmiComm INT NOT NULL,
      JoinDate DATE NOT NULL,
      FacultyID INT NOT NULL
    )

CREATE TABLE TeacherDetails (

TeacherID INT NOT NULL,

TeacherName VARCHAR(60),

FatherName VARCHAR(60),

Education VARCHAR(60),

Position VARCHAR(60),

TotalSalary INT NOT NULL,

AdmiComm INT NOT NULL,

JoinDate DATE NOT NULL,

FacultyID INT NOT NULL

)

TeacherID	TeacherName	FatherName	Education	Position	TotalSalary	AdmiComm	JoinDate	FacultyID
101	Liam Isaiah	Noah Charles	Graduate degree	Professional	45000	3421	2015-03-21	3
102	William Josiah	James Hudson	Bachelors	Professional	55000	1534	2016-04-11	4
103	Oliver Christian	Benjamin Hunter	Master Degree	Management	65000	6541	2016-04-18	2
104	Elijah Connor	Lucas Eli	Bachelors	Management	44000	650	2016-03-24	3
105	Mason Ezra	Logan Aaron	B.ED	Clerical	64000	2511	2016-04-09	6
106	Alexander Landon	Ethan Adrian	Graduate Degree	Professional	48000	1127	2016-04-15	1
107	Jacob Jonathan	Michael Nolan	Education	Management	24000	2341	2016-04-22	7
108	Daniel Jeremiah	Henry Easton	Master Degree	Instructor	34000	6543	2016-04-26	4
109	Jackson Elias	Sebastian Colton	Management	Professional	71000	874	2016-04-28	2
110	Owen Dominic	John Greyson	B.ED	Professional	39000	795	2016-05-07	4
111	Jack Adam	Luke Austin	Education	Clerical	25000	611	2016-05-10	5
112	Dylan Santiago	Grayson Jordan	Bachelors	Professional	36000	4523	2016-05-18	7
113	Gabriel Roman	Julian Evan	Management	Instructor	67000	8312	2016-05-21	3
114	Mateo Ezekiel	Anthony Xavier	InterMediate	Clerical	23000	1546	2016-05-29	1
115	Jaxon Jose	Lincoln Jace	Bachelors	Management	35000	3450	2016-06-06	5
116	Joshua Jameson	Christopher Leonardo	Bachelors	Professional	38000	4311	2016-06-11	1
117	Andrew Bryson	Theodore Axel	Management	Instructor	64000	8991	2016-06-17	8
118	Caleb Everett	Ryan parker	High School	Clerical	21000	2100	2016-06-22	4
119	Asher Kayden	Nathan Miles	B.ED	Management	58000	4500	2016-06-25	3
120	Thomas sawyer	Leo Jason	Masters Degree	Management	66000	4326	2016-06-30	8

Demo Table: Faculty

CREATE TABLE Faculty (
      ID INT NOT NULL,
      FacultySubject VARCHAR(60),
    )

CREATE TABLE Faculty (

ID INT NOT NULL,

FacultySubject VARCHAR(60),

)

ID	FacultySubject
1	Account
2	Economics
3	Geography
4	Mathematics
5	English
6	History
7	Science
8	Social Science

Create SQL Scalar Function

In SQL Server, the Scalar User-defined functions are very useful, Therefore, when you want to return a single value as the resultant.
let’s see, for example, total investments, total sales, total profit, total loss, total turnover, or total expenditure, etc.

Create SQL Scalar Function With No Parameters

Therefore, in this simple SQL scalar function, you will see how to create the Scalar function without any parameters.
there are the following below given query, you can observe that we are summing the TotalSalary of the TeacherDetails table.

---SQL User Defined Functions: - SQL Scalar Function with No Parameters example
USE Schools;
GO
CREATE FUNCTION NoParameters()
  RETURNS INT
  AS
    BEGIN 
       RETURN (SELECT SUM([TotalSalary]) FROM [TeacherDetails])
    END

---SQL User Defined Functions: - SQL Scalar Function with No Parameters example

USE Schools;

CREATE FUNCTION NoParameters()

RETURNS INT

BEGIN

RETURN (SELECT SUM([TotalSalary]) FROM [TeacherDetails])

END

SQL User Defined Functions
furthermore, Let me show you, that how it looks in the SQL Server Management Studio

I hope you will understand, therefore, Let us see the SQL scalar function output result.

SELECT [TeacherID]
      ,[TeacherName]
      ,[FatherName]
      ,[Education]
      ,[TotalSalary]
      ,dbo.NoParameters() AS [Average Salary]
      ,[JoinDate]
  FROM [TeacherDetails]

SELECT [TeacherID]

,[TeacherName]

,[FatherName]

,[Education]

,[TotalSalary]

,dbo.NoParameters() AS [Average Salary]

,[JoinDate]

FROM [TeacherDetails]

SQL scalar function

Create SQL Scalar Function With Parameters

In this example, you will see that How to create SQL Scalar function with parameters. therefore, there are the given below query you can observe that, we are the Concating TeacherName and FatherName.

-- SPACE in SQL example
DECLARE @TeacherName VARCHAR(60), 
        @FatherName VARCHAR(60)
SET @TeacherName = 'name' 
SET @FatherName = 'Details'

-- SQL User Defined Functions - Scalar Functions example
USE CarShowroom;
CREATE FUNCTION nameDetails (@TeacherName VARCHAR(60), @FatherName VARCHAR(60))
  RETURNS VARCHAR(200)
  AS
    BEGIN 
       RETURN (SELECT  @TeacherName + SPACE(2) + @FatherName )
    END

-- SPACE in SQL example

DECLARE @TeacherName VARCHAR(60),

@FatherName VARCHAR(60)

SET @TeacherName = 'name'

SET @FatherName = 'Details'

-- SQL User Defined Functions - Scalar Functions example

USE CarShowroom;

CREATE FUNCTION nameDetails (@TeacherName VARCHAR(60), @FatherName VARCHAR(60))

RETURNS VARCHAR(200)

BEGIN

RETURN (SELECT @TeacherName + SPACE(2) + @FatherName )

END

Furthermore, Let us see the Output by using the given below query

USE Schools;
GO
SELECT [TeacherID]
       -- Passing Parameters to fullname Function
      ,dbo.nameDetails([TeacherName], [FatherName]) AS [Name]
      ,[Education]
      ,[TotalSalary]
      ,[JoinDate]
  FROM [TeacherDetails]

USE Schools;

SELECT [TeacherID]

-- Passing Parameters to fullname Function

,dbo.nameDetails([TeacherName], [FatherName]) AS [Name]

,[Education]

,[TotalSalary]

,[JoinDate]

FROM [TeacherDetails]

SQL Scalar Function with parameter

To Create SQL Scalar Function Where Clause

There is the following example for SQL scalar function where clause, it will accept the varchar as the parameter.
And it finds the sum of the Admission Commission amount, whose Position is equal to the parameter that we pass.

CREATE FUNCTION average (@Position VARCHAR(50))
  RETURNS FLOAT
  AS
    BEGIN 
       RETURN (SELECT  SUM([AdmiComm]) FROM [TeacherDetails]
	        WHERE [Position] = @Position)
    END

CREATE FUNCTION average (@Position VARCHAR(50))

RETURNS FLOAT

BEGIN

RETURN (SELECT SUM([AdmiComm]) FROM [TeacherDetails]

WHERE [Position] = @Position)

END

Therefore, let us see the Output

USE Schools;
GO
SELECT [Position]
      ,SUM([TotalSalary]) as [Total Salary]
      ,SUM([AdmiComm]) AS [Total Admission Commision]
      ,dbo.average([Position]) AS [Total Commission from Function]
  FROM [TeacherDetails]
  group by [Position]

USE Schools;

SELECT [Position]

,SUM([TotalSalary]) as [Total Salary]

,SUM([AdmiComm]) AS [Total Admission Commision]

,dbo.average([Position]) AS [Total Commission from Function]

FROM [TeacherDetails]

group by [Position]

SQL Scalar Function Where Clause

Example For To Use SQL Scalar Function in Where Clause

therefore, in this example, you will see How to use the SQL Scalar function in WHERE Clause

USE Schools;
GO
CREATE FUNCTION AverageTotalSalary ()
  RETURNS FLOAT
  AS
    BEGIN 
       RETURN (SELECT  AVG([TotalSalary]) FROM [TeacherDetails])
    END

USE Schools;

CREATE FUNCTION AverageTotalSalary ()

RETURNS FLOAT

BEGIN

RETURN (SELECT AVG([TotalSalary]) FROM [TeacherDetails])

END

Therefore, let us see the Output

USE Schools;
GO
SELECT [TeacherID]
      ,[TeacherName]
      ,[FatherName]
      ,[Education]
      ,[Position]
      ,[TotalSalary]
      ,[AdmiComm]
      ,[JoinDate]
  FROM [TeacherDetails]
  WHERE [TotalSalary] >= dbo.AverageTotalSalary()

USE Schools;

SELECT [TeacherID]

,[TeacherName]

,[FatherName]

,[Education]

,[Position]

,[TotalSalary]

,[AdmiComm]

,[JoinDate]

FROM [TeacherDetails]

WHERE [TotalSalary] >= dbo.AverageTotalSalary()

SQL User Defined Functions

Example For To Create SQL Server Inline Table-valued Functions

Therefore, In SQL Server Inline function returns a table data type as the return value which is based on a single SELECT Statement

Example For To Create SQL Inline Function with No parameters

Therefore, in this simple example, you will see how to create a SQL Inline table-valued function without any parameters.
There are the given below query, In this query, you can notice that we are selecting the top 11 records from the TeacherDetails table.

-- SQL User Defined Functions - Inline Functions example
USE Schools;
GO
CREATE FUNCTION TopElevenTeachers ()
  RETURNS TABLE
  AS
       RETURN (
		   SELECT TOP 11 [TeacherName]
		  ,[FatherName]
		  ,[Education]
		  ,[Position]
		  ,[TotalSalary]
		  ,[AdmiComm]
		  ,[JoinDate]
		  FROM [TeacherDetails]
               )

-- SQL User Defined Functions - Inline Functions example

USE Schools;

CREATE FUNCTION TopElevenTeachers ()

RETURNS TABLE

RETURN (

SELECT TOP 11 [TeacherName]

,[FatherName]

,[Education]

,[Position]

,[TotalSalary]

,[AdmiComm]

,[JoinDate]

FROM [TeacherDetails]

)

Therefore, let us see the Output

USE Schools;
GO
SELECT * FROM [dbo].[TopElevenTeachers] ()
GO

USE Schools;

SELECT * FROM [dbo].[TopElevenTeachers] ()

SQL Inline Function With No Parameters

Example For SQL Inline Function with Parameters

Therefore, In SQL Server functions example you will see how to create an SQL Inline table-valued function with parameters.
There are the given below the following SQL below query, you will notice that we are selecting the records from both the table using INNER JOIN, whose Position is equal to a parameter that we pass.

-- SQL User Defined Functions - Inline Functions example
USE Schools;
GO
CREATE FUNCTION TeacherbySubject (@profession VARCHAR(50))
  RETURNS TABLE
  AS
     RETURN (
		SELECT  [TeacherName]
        ,[FatherName]
		,[Position]
		,[Education]
		,dept.FacultySubject AS Department
		,[TotalSalary] AS Salary
		,[AdmiComm]
		FROM [TeacherDetails]
		INNER JOIN 
		Faculty AS dept ON
		  Dept.[id] = [TeacherDetails].FacultyID
		WHERE [Position] = @profession
		)

-- SQL User Defined Functions - Inline Functions example

USE Schools;

CREATE FUNCTION TeacherbySubject (@profession VARCHAR(50))

RETURNS TABLE

RETURN (

SELECT [TeacherName]

,[FatherName]

,[Position]

,[Education]

,dept.FacultySubject AS Department

,[TotalSalary] AS Salary

,[AdmiComm]

FROM [TeacherDetails]

INNER JOIN

Faculty AS dept ON

Dept.[id] = [TeacherDetails].FacultyID

WHERE [Position] = @profession

)

Furthermore, see the Output result of the SQL Inline table-valued function

USE Schools;
GO
SELECT * FROM [dbo].[TeacherbySubject] ('Professional')
GO

USE Schools;

SELECT * FROM [dbo].[TeacherbySubject] ('Professional')

SQL Inline Function with Parameters

Example For Multiple User Defined Functions in SQL Server

Therefore, here you will learn How to use multiple User-defined functions in a single SELECT Statement.

USE Schools;
GO
SELECT [TeacherID]
      ,dbo.nameDetails([TeacherName], [FatherName]) AS Name --  First UDF
      ,[Education]
      ,[Position]
      ,[TotalSalary]
      ,[AdmiComm]
      ,[JoinDate]
  FROM [TeacherDetails]
  WHERE [TotalSalary] >= dbo.AverageTotalSalary() -- Second UDF

USE Schools;

SELECT [TeacherID]

,dbo.nameDetails([TeacherName], [FatherName]) AS Name -- First UDF

,[Education]

,[Position]

,[TotalSalary]

,[AdmiComm]

,[JoinDate]

FROM [TeacherDetails]

WHERE [TotalSalary] >= dbo.AverageTotalSalary() -- Second UDF

Multiple SQL User Defined Functions

Examples For Multi-select Table-valued Functions in SQL Server

There is the following SQL Server Multiple select table-valued functions return the tabular result set. therefore, however, unlike the inline table-valued function, so, we can use multiple select statements inside the function body.

furthermore, I will show you an example of SQL Server Multi-select table-valued function and How to use Multiple statements in one User-defined function.

-- SQL User-Defined Functions - Table-Valued Functions example
USE Schools;
GO
CREATE FUNCTION TeacherDepartment()
  RETURNS @Teacher TABLE
  (
  	[TeacherID] [smallint] NOT NULL,
	[TeacherName] [nvarchar](60) NULL,
	[FatherName] [nvarchar](60)  NULL,
	[Education] [nvarchar](255) NULL,
	[Position] [nvarchar](255) NULL,
	[TotalSalary] [float] NULL,
	[AdmiComm] [float] NULL,
	[JoinDate] [date] NULL,
	[FacultySubject] [VARCHAR](50) NULL
  )
  AS BEGIN
   INSERT INTO @Teacher
   SELECT  [TeacherID]
	  ,[TeacherName]
      ,[FatherName]
	  ,[Education]
	  ,[Position]
	  ,[TotalSalary]
	  ,[AdmiComm]
	  ,[JoinDate]
	  ,dept.FacultySubject
	FROM [TeacherDetails]
	INNER JOIN 
	     Faculty AS dept ON
		Dept.[id] = [TeacherDetails].FacultyID
    
   -- Updating the Records
   UPDATE @Teacher SET [TotalSalary] = [TotalSalary] + 30300
	WHERE [AdmiComm] > (SELECT AVG(AdmiComm) FROM [TeacherDetails])
  RETURN
 END

-- SQL User-Defined Functions - Table-Valued Functions example

USE Schools;

CREATE FUNCTION TeacherDepartment()

RETURNS @Teacher TABLE

(

[TeacherID] [smallint] NOT NULL,

[TeacherName] [nvarchar](60) NULL,

[FatherName] [nvarchar](60) NULL,

[Education] [nvarchar](255) NULL,

[Position] [nvarchar](255) NULL,

[TotalSalary] [float] NULL,

[AdmiComm] [float] NULL,

[JoinDate] [date] NULL,

[FacultySubject] [VARCHAR](50) NULL

)

AS BEGIN

INSERT INTO @Teacher

SELECT [TeacherID]

,[TeacherName]

,[FatherName]

,[Education]

,[Position]

,[TotalSalary]

,[AdmiComm]

,[JoinDate]

,dept.FacultySubject

FROM [TeacherDetails]

INNER JOIN

Faculty AS dept ON

Dept.[id] = [TeacherDetails].FacultyID

-- Updating the Records

UPDATE @Teacher SET [TotalSalary] = [TotalSalary] + 30300

WHERE [AdmiComm] > (SELECT AVG(AdmiComm) FROM [TeacherDetails])

RETURN

END

TO ANALYSIS

Furthermore, the Example, Within this User Defined Functions First, we are creating a table variable called @Teacher using the following statement.

@Teacher TABLE

1	@Teacher TABLE

Therefore, Next, we are inserting the records from [TeacherDetails], and [Faculty] table into @Teacher table variable.

INSERT INTO @Teacher
   SELECT

1 2	INSERT INTO @Teacher SELECT

Therefore, In the Next step, we are updating the Total Salary of all the Teachers present in the @teacher table variable, whose TotalSalary is greater than the average AdmiComm (admission Commission).

UPDATE @Teacher SET [TotalSalary] = [TotalSalary] + 30300
WHERE [AdmiComm] > (SELECT AVG(AdmiComm) FROM [TeacherDetails])

1 2	UPDATE @Teacher SET [TotalSalary] = [TotalSalary] + 30300 WHERE [AdmiComm] > (SELECT AVG(AdmiComm) FROM [TeacherDetails])

Furthermore, let us see the Output

USE Schools;
GO
SELECT * FROM [dbo].[TeacherDepartment] ()
GO

USE Schools;

SELECT * FROM [dbo].[TeacherDepartment] ()

Multi-select Table-valued Functions in SQL Server

One UDF inside another UDF in SQL Server

There are the following SQL server UDF (User Defined Functions) example, you will see that, How to nest or insert one User-defined function inside another SQL User-defined function.

USE Schools;
GO
CREATE FUNCTION TeachDepartment (@education VARCHAR(50))
  RETURNS TABLE
  AS
   RETURN (
	SELECT dbo.nameDetails([TeacherName], [FatherName]) AS Name
	,[Position]
	,[Education]
	,dept.FacultySubject AS Faculty
	,[TotalSalary] AS Salary
	,[AdmiComm]
	FROM [TeacherDetails]
	INNER JOIN 
	Faculty AS dept ON
	  Dept.[id] = [TeacherDetails].FacultyID
	WHERE [Education] = @education
)

USE Schools;

CREATE FUNCTION TeachDepartment (@education VARCHAR(50))

RETURNS TABLE

RETURN (

SELECT dbo.nameDetails([TeacherName], [FatherName]) AS Name

,[Position]

,[Education]

,dept.FacultySubject AS Faculty

,[TotalSalary] AS Salary

,[AdmiComm]

FROM [TeacherDetails]

INNER JOIN

Faculty AS dept ON

Dept.[id] = [TeacherDetails].FacultyID

WHERE [Education] = @education

)

Therefore, here, we can see the Output of the Nested user-defined functions in SQL Server

USE Schools;
GO
SELECT * FROM [dbo].[TeachDepartment] ('Management')

USE Schools;

SELECT * FROM [dbo].[TeachDepartment] ('Management')

One SQL User Defined Functions

Advantages of User-Defined Functions

In SQL Server User-defined functions prevent us from writing the same logic multiple times.
You can use SQL Server Functions in WHERE Clause as well. furthermore, By this, we can limit the number of rows sent to the client.
These user-defined functions can help us to separate the difficult or complex calculations from the regular query therefore, we can understand and debug the query quicker and better.
In the SQL Server database, you can create the function once times, and call it n number of times.
SQL user-defined functions reduce the compilation time of the query by catching the execution plan.
It reduces the network traffic because of its cache plan

Limitations of User-Defined Functions

There are the list of limitations of the SQL User-defined Functions in SQL Server

First, it cannot be used User-defined functions in SQL Server, shortly called as UDF, to modify the database state.
In SQL Server UDF FOR XML Clause is not permitted inside the functions
We cannot call a Stored Procedure from SQL User Defined Function (UDF), but we can call an extended Stored Procedure
The SQL UDF (SQL User Defined Functions) can not return multiple result sets.
SET statements are not allowed in SQL User-Defined Functions
The SQL UDF does not support error handlings, such as RAISEERROR, @ERROR, or TRY…CACHE
The SQL UDF does not support the temporary tables, but it will allow the Table variable.

Conclusion

I hope this article provides a comprehensible approach on how to Use SQL user-defined Function in SQL server. therefore, User-defined functions (UDF) in SQL Server 2008 allow you to create reusable routines, it helps make your SQL code more straight forward and efficient.
The Table-valued functions provide a way to create what are essentially parameterized views, furthermore, you can include them inline in your queries, just as you would in a view or table.
Always ensure the precision of the numeric or decimal variable specified is enough to accommodate the values assigned to it.
In this article, we observed that how selecting the right kind of data type helps SQL developers to save disk storage
Hope you will enjoy this topic.

SQL User Defined Functions | What are SQL user Defined Functions

Why use SQL User-Defined Functions

Types of Functions in SQL Server

Syntax of User-Defined Functions in SQL Server

How to Create Different Types of SQL User-Defined Functions

Demo Table: TeacherDetails

Demo Table: Faculty

Create SQL Scalar Function

Create SQL Scalar Function With No Parameters

Create SQL Scalar Function With Parameters

To Create SQL Scalar Function Where Clause

Example For To Use SQL Scalar Function in Where Clause

Example For To Create SQL Server Inline Table-valued Functions

Example For To Create SQL Inline Function with No parameters

Example For SQL Inline Function with Parameters

Example For Multiple User Defined Functions in SQL Server

Examples For Multi-select Table-valued Functions in SQL Server

TO ANALYSIS

One UDF inside another UDF in SQL Server

Advantages of User-Defined Functions

Limitations of User-Defined Functions

Conclusion

Related Posts

About Author

admin