User Defined Functions in SQL Server

Type of SQL User Defined Functions

Scalar Function:

Scalar functions returns only scalar/single value. Which can be used in the Select statement, Where, Group By, Having clause. It returns a single data value of the type mentioned in the RETURNS clause.

Create Scalar function by using the below query

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION GetOrderTotal
(
	@OrderID INT
)
RETURNS DECIMAL
AS
BEGIN
	
	DECLARE @Total DECIMAL 

	SELECT @Total = SUM([UnitPrice] * [Quantity]) 
        FROM [Northwind].[dbo].[Order Details]
        WHERE OrderID = @OrderID
	
	RETURN @Total

END
GO            
            

You can use below inline query, Select statement, Where clause to execute this function.

SELECT dbo.GetOrderTotal(10248)

//Select Statement
SELECT OrderID, CustomerID, ShipCountry, 
    dbo.GetOrderTotal(OrderID) AS 'Order Total'  FROM Orders

//In where clause
SELECT OrderID, CustomerID, ShipCountry, 
    dbo.GetOrderTotal(OrderID) AS 'Order Total'  FROM Orders
    WHERE dbo.GetOrderTotal(OrderID) >= 500
    
            

Table Valued Functions

Table valued function introduced in SQL Server 2005. It returns a resultset as a form of the Table variable. This can be a good alternative to View as View does not allow parameters whereas Table Valued Functions allowed parameters.

The Table returned by UDF can be used in From clause whereas the result set returned by Stored Procedure cannot use in From clause.

The body of Table valued UDF is just an inline query that use Select statement and returns a resultset.

Create Table valued UDF by using the below query

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION CustomerProductDetails
(	
	@CustomerID NCHAR(5)
)
RETURNS TABLE 
AS
RETURN 
(
	SELECT C.CustomerID, C.CompanyName, C.City, O.OrderID, O.OrderDate,
	P.ProductName, OD.UnitPrice, OD.Quantity, OD.Discount FROM [Products] P
	INNER JOIN [Order Details] OD ON P.ProductID = OD.ProductID
	INNER JOIN Orders O ON O.OrderID = OD.OrderID
	INNER JOIN Customers C ON C.CustomerID = O.CustomerID
	WHERE C.CustomerID = @CustomerID
)
GO
    

You can use this function in From clause or in JOIN. For more details see Types of SQL Server JOINS

SELECT * FROM dbo.CustomerProductDetails('VINET')

//Join with Employee Table
SELECT E.EmployeeID, E.LastName, E.FirstName, E.Title, 
        CP.CustomerID, CP.CompanyName, CP.City,  
        CP.OrderID, CP.OrderDate, CP.EmployeeID,
	    CP.ProductName, CP.UnitPrice, CP.Quantity, CP.Discount  
    FROM [Employees] E
    JOIN dbo.CustomerProductDetails('VINET') CP ON 
	CP.EmployeeID = E.EmployeeID
    
        

You will see the below output when you join Table valued function

Multi-Statement Table Valued Function

A Multi-Statement Table-Valued user-defined function returns a table. It can execute one or more than one T-SQL statements. Body of function command you must define the table structure that is being returned. This type of UDF allows you to have more complex logic than Table Valued UDF. After creating this kind of UDF, you can use it in the FROM clause of a select statement unlike the behavior a stored procedure which can also return record sets.

Use the below query to create Multi Statement Table Valued Function.

CREATE FUNCTION CustomerOrderDetails
( 
	@CustomerID NCHAR(5) 
)
RETURNS
@CustomerOrders table (
    CustomerID NCHAR(5),
    CompanyName NVARCHAR(40),
    OrderID INT,
    OrderDate DATETIME
)
AS
BEGIN
 
INSERT INTO @CustomerOrders
SELECT  C.CustomerID, C.CompanyName,  O.OrderID, O.OrderDate FROM 
	    Customers C
	    INNER JOIN Orders O ON C.CustomerID = C.CustomerID
	    WHERE C.CustomerID = @CustomerID
 
IF @ROWCOUNT = 0
BEGIN
INSERT INTO @CustomerOrders
VALUES ('','No Orders Found',0,GETDATE())
END
 
RETURN
END
GO
    
          

Benefits of SQL User Defined Functions

1. The flexibility of using UDF output

   SQL UDF can be executed through Select statements or inline queries. The select statement can show scalar UDF output as Select resultset.
   Scalar functions can be used in SELECT, WHERE, HAVING clause, and Table valued functions can be used in FROM, JOIN, CROSS APPLY clause. See more examples about Cross Apply and Outer apply with examples

2. Shorter Code

   It gives the ability to write separate code blocks for complex logic and includes in the main query. This makes code less complex, easy to write, and maintain.

3. Faster execution

   Like stored procedures functions reduce compilation cost of T-SQL code by caching execution plan and reusing it for subsequent executions. It does not need to reparsed and re optimized with each execution resulting in much faster execution.

Limitations of SQL User Defined Functions

1. Non-Deterministic Built-in Functions like GETDATE or RAND cannot be used in UDF.
2. UDF return only one value or resultset. In stored procedure you can have multiple select statements to return multiple resultset however in UDF this is not possible.
3. UDF can not call Stored Procedure, it can call only extended stored procedures.
4. Dynamic SQL queries are now allowed in UDF.
5. In UDF you cannot create Temporary tables.
6. For XML is not supported in UDF.
7. As UDF does not support error handling you cannot use RAISEERROR or @ERROR. You will have to handle errors in calling sql by using exception handling with try catch block.

Summary SQL UDFs are routines, it can perform complex logic, calculations, have one or more parameters and return either a scalar value or a Table datatype. This article shows to create various type of User Defined Functions and execute them.