User Defined Functions in SQL Server 11

User Defined Functions in SQL

The UDF or User Defined Functions in SQL Server are like methods in any other programming language that accepts the parameters, performs complex calculations, and returns the result value.

Types of Functions in SQL Server

There are two types of SQL Server functions:

Built in Functions

All the built ins supported by the SQL Server called System functions. We don’t have to bother about the logic inside them because they cannot be modified. For example, Mathematical, Ranking, String are some of the many built in functions.

The aggregate functions to find sum, minimum value, average value are mostly used system methods. And finding the current system date and time is also the most frequent one.

User Defined Functions

SQL Server allows us to create our methods called as user defined functions. For example, if we want to perform some complex calculations, then we can place them in a separate method, and store it in the database. Whenever we need the calculation, we can call it. There are three types of SQL functions:

Scalar: It returns a single value. Generally, we have to define the body between BEGIN … END block, but for inline scalar function, you can omit them. We can use any data type as the return type except text, image, ntext, cursor, and timestamp.

Table Valued: It is a user defined function that returns a table.

Inline Table valued: It returns a table data type based on a single SELECT Statement

Advantages of UDFs

  1. The SQL Server User defined functions prevent us from writing the same logic multiple times.
  2. Within the Database, you can create the method once, and call it n number of times.
  3. SQL functions reduce the compilation time of query by catching the execution plan and reusing them.
  4. This UDF 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. They also used in WHERE Clause as well. By this, we can limit the number of rows sent to the client.

SQL User Defined Functions Syntax

The syntax the SQL Server functions or UDF is

CREATE 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
  • 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 method 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 method.

Let us see how to create or write different types of Functions with example.

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

TIP: Please refer to Alter UDFs 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

Source Table

And [Department] table has eight records.

Department Table

Create SQL Scalar Function example

The SQL Scalar functions 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 example, we will show you how to create the Sum SQL Scalar functions without any parameters.

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

-- Scalar example
CREATE FUNCTION NoParameters ()
  RETURNS INT
  AS
    BEGIN 
       RETURN (SELECT SUM([YearlyIncome]) FROM [MyEmployees Table])
    END
Messages
-------
Command(s) completed successfully.

Let me show you, How our newly created one looks in the Management Studio

SQL Scalar Functions 3

Let us see the Output of the Sum SQL scalar functions

SELECT [EmployeeID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,dbo.NoParameters() AS [Average Income]
      ,[Sales]
      ,[HireDate]
  FROM [MyEmployees Table]
SQL Scalar Function Result 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 to provide the space between the First name and last name.

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

SELECT [EmployeeID]
       -- Passing Parameters to fullname Function
      ,dbo.fullname([FirstName], [lastName]) AS [Name]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [MyEmployees Table]
Scalar Function with parameters

Create SQL Scalar Function Where Clause example

The following SQL scalar functions 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.

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

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]
Scalar Function Where Clause example 6

Use SQL Scalar Functions in Where Clause example

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

CREATE FUNCTION AverageSale ()
  RETURNS FLOAT
  AS
    BEGIN 
       RETURN (SELECT  AVG([Sales]) FROM [MyEmployees Table])
    END

Let us see the Output

SELECT [EmployeeID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [MyEmployees Table]
  WHERE [Sales] >= dbo.AverageSale()
SQL Scalar Function in Where Clause 7

Create SQL Server Inline Table valued Functions examples

The Inline UDF 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.

-- Inline example

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

SELECT * FROM [dbo].[TopTenCustomers] ()
GO
Inline Table valued Function Example 8

SQL Inline Function with Parameters Example

This SQL Server example shows how to create an Inline table valued functions 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.

-- Inline example

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

SELECT * FROM [dbo].[CustomerbyDepartment] ('Management')
GO
SQL Inline Function with Parameters Example 9

Multi-select Table valued Functions examples

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

In this SQL Server Multi select table valued example will show you, how to use Multiple statements in one UDF.

-- Table Valued example

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

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

SELECT * FROM [dbo].[CustomerDepartment] ()
GO
SQL Table Valued Function Example 10

Multiple User Defined Functions in SQL Server

How to use multiple UDFs in a single SELECT Statement.

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

One UDF inside another UDF

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

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 functions in Sql Server

SELECT * FROM [dbo].[CustDepartment] ('Bachelors')
One SQL UDF inside another 12

Limitations of Functions in SQL Server

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

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

Comments are closed.