Tutorial Gateway

User Defined Functions in SQL

by

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 SQL are called as System function. We don’t have to bother about the logic inside them because they cannot be modified. For example, Mathematical Functions, Ranking Functions, String Functions, and so on.
  2. User Defined Functions: SQL Server allows us to create our own functions called as user defined functions. For example, if we want to perform some complex calculations then we can place them in 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 return 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 function that return a table.
      • Inline Table valued Functions: This function returns a table data type based on a single SELECT Statement
      • Multi Statement Table valued Functions: This function also returns the tabular result set but, unlike the inline table valued function we can use multiple select statements inside the function body.

Advantages of User Defined Functions in SQL Server

  1. It will prevent us from writing 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 SQL query by catching the execution plan and reusing them.
  4. It can help us to separate the complex calculations from the regular SQL query so that, we can understand and debug the query quicker and better.
  5. It reduce the network traffic because of its cache plan
  6. Functions can be 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 basic syntax behind the SQL Server User defined functions is as shown below:

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. This can be 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’s 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 SQL query, or any complex mathematical calculations you want to implement in this particular function.

In this article we will show you, How to write different types of User Defined Functions in SQL Server with example. For this demonstration, We are going to use [MyEmployee table] and [Department] table present in our [SQL Tutorial] Database.

TIP: Please refer Alter User Defined Functions in SQL Server article to understand, How to Rename, Modify, or Delete 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 have eight records.

User Defined Functions in SQL Server 0

Scalar Functions examples

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.

Scalar Function with No parameters

In this simple 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 CODE

-- SQL User Defined Functions - Scalar Functions example
USE [SQL Tutorial]
GO
CREATE FUNCTION NoParameters ()
  RETURNS INT
  AS
    BEGIN 
       RETURN (SELECT SUM([YearlyIncome]) FROM [MyEmployees Table])
    END

OUTPUT

User Defined Functions in SQL Server 2

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

User Defined Functions in SQL Server 3

Let us see the Output

SQL CODE

USE [SQL Tutorial]
GO
SELECT [EmployeeID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,dbo.NoParameters() AS [Average Income]
      ,[Sales]
      ,[HireDate]
  FROM [MyEmployees Table]

OUTPUT

User Defined Functions in SQL Server 4

Scalar Function with parameters

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

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

SQL CODE

-- SQL User Defined Functions - Scalar Functions example
USE [SQL Tutorial]
GO
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

SQL CODE

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]

OUTPUT

User Defined Functions in SQL Server 5

Scalar Function with Where Clause

The following example will accept the var char as the parameter, and finds the sum of the Sales amount, whose Occupation is equal to the parameter that we pass.

SQL CODE

-- SQL User Defined Functions - Scalar Functions example
USE [SQL Tutorial]
GO
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

SQL CODE

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]

OUTPUT

User Defined Functions in SQL Server 6

Use Scalar Function in Where Clause

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

SQL CODE

-- SQL User Defined Functions - Scalar Functions 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

SQL CODE

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

OUTPUT

User Defined Functions in SQL Server 7

Inline Table valued Functions examples

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

Inline Function with No parameters

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

SQL CODE

-- 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

SQL CODE

USE [SQL Tutorial]
GO
SELECT * FROM [dbo].[TopTenCustomers] ()
GO

OUTPUT

User Defined Functions in SQL Server 8

Inline Function with Parameters

This example will show you, How to create an Inline table valued function with parameters. From the below query you can observe that, we are selecting the records from the MyEmployee table, Department table using INNER JOIN, whose Occupation is equal to parameter that we pass..

SQL CODE

-- 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

SQL CODE

USE [SQL Tutorial]
GO

SELECT * FROM [dbo].[CustomerbyDepartment] ('Management')
GO

OUTPUT

User Defined Functions in SQL Server 9

Multi-select Table valued Functions examples

The Multiple select table valued functions returns the tabular result set but, unlike the inline table valued function we can use multiple select statements inside the function body. In this example we will show you, How to use Multiple statements in one User defined function.

SQL CODE

-- 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

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

SQL CODE

USE [SQL Tutorial]
GO

SELECT * FROM [dbo].[CustomerDepartment] ()
GO

OUTPUT

User Defined Functions in SQL Server 10

Multiple User Defined Function in SQL Server

In this simple example we will show you, How to use multiple User defined functions in single SELECT Statement.

SQL CODE

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

OUTPUT

User Defined Functions in SQL Server 11

One UDF inside another UDF in SQL Server

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

SQL CODE

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

SQL CODE

USE [SQL Tutorial]
GO

SELECT * FROM [dbo].[CustDepartment] ('Bachelors')

OUTPUT

User Defined Functions in SQL Server 12

Limitations of User Defined Functions in SQL Server

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

Thank you for Visiting Our Blog

Placed Under: SQL