User-Defined Functions in Sql Server

User-defined functions (UDFs) are routines that accept parameters, perform complex functions, and return a value or the result set.

Deepak Talwar Deepak Talwar
May 5, 2022 - 11:24 Updated: Apr 5, 2024 - 11:25
0 22
User-Defined Functions in Sql Server
UDF, User-defined function, Sql Server, Database, thetechfoyer, thetechfoyer.com

This Articles helps you to understand the concept of User-Defined Functions in Sql Server

There are three types of UDFs – user-defined scalar functions, table-valued functions, and system functions.



We use UDFs because:

  • They can be created, stored, and called at any number of times.
  • They allow faster execution because UDFs don’t need to be reparsed or reoptimized.
  • They minimize network traffic as it reduces the number of rows sent to the client.



Table-Valued User Defined function
A table-valued function is a user-defined function that returns data of a table type. The return type of a table-valued function is a table, therefore, you can use the table-valued function just like you would use a table.

-- A User Defined table-valued function
CREATE FUNCTION [dbo].[SplitString]
(    
    @Input NVARCHAR(MAX),
    @Character CHAR(1)
)
RETURNS @Output TABLE (
    Item NVARCHAR(1000)
)
AS
BEGIN
    DECLARE @StartIndex INT, @EndIndex INT
 
    SET @StartIndex = 1
    IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
    BEGIN
        SET @Input = @Input + @Character
    END
 
    WHILE CHARINDEX(@Character, @Input) > 0
    BEGIN
        SET @EndIndex = CHARINDEX(@Character, @Input)
         
        INSERT INTO @Output(Item)
        SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)
         
        SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
    END
 
    RETURN
END

--Calling above UDf Table Valued function
SELECT * FROM DBO.[SplitString]('One,Two,Three,Four,Five',',')



Scalar User Defined functions
Scalar User Defined functions are commonly used in SQL Server to simplify complex queries and to encapsulate business logic.
They can be used in queries just like any other SQL function, and their return value can be used as a column value, a part of a where clause, or in any other expression.

-- A User Defined scalar function
CREATE FUNCTION CalculateRowTotal
(@Price decimal(10,2),@Quantity int,@Discount decimal(10,2))
RETURNS decimal(10,2)
AS
BEGIN
   RETURN (@Price-@Discount) * @Quantity
END

--Calling above UDf Scalar function
SELECT ProductName,Quantity,price,
dbo.CalculateRowTotal(Price, Quantity, 100)
AS Total
FROM Products


-- Using in Where Clause
SELECT * FROM Products
WHERE dbo.CalculateRowTotal(Price, Quantity, 100)  > 1000