Sql Server

SQL | User-Defined Functions

by Deepak Talwar
September 5, 2023
0 Comments
3 minute read
158 Views
0 Likes

User-defined functions (UDFs) are routines that accept parameters, perform complex functions, and return a value or the result set.
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 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

System Functions:
Pre-defined functions that are defined by the system are known as system functions.

SELECT CAST('2023-09-25' AS datetime);

Deepak Talwar

Technical Architect and Full Stack Developer with 18+ years of Professional Experience in Microsoft Technologies & PHP Platform. Hands on experience with C#, VB, ASP.NET, ASP.NET MVC, ASP.NET Core, ASP.NET Web API, Linq, ADO.NET, Entity Framework, Entity Framework Core, Sql Server, MYSQL, NoSql, Javascript, Angular, jQuery, AWS, Azure, React, Angular, Laravel, Codeingiter, Serenity, VBA, Cloud Computing, Microservices, Design Patterns, Software Architecture, Web Design and Development.

SQL | COALESCE Function

September 2, 2023

Sql | Event Notifications

September 5, 2023

Your Name *

E-mail *

Website

Comments *

Design

SQL | User-Defined Functions

Deepak Talwar

SQL | What are Views

SQL | DDL, DQL, DML, DCL and TCL Commands

SQL | List Stored Procedure

The Most Powerful

Stay Connected

Popular

What is SQL Server

SQL | List Stored Procedure

How Cursors Works in Sql Server

FOR XML PATH clause in SQL Server

Sql Server | Find String | CHARINDEX, LIKE

Hot Categories

Tags

React Custom Hooks

React useMemo Hook

About Us

Instagram

Contact Info

SQL | User-Defined Functions

Popular Tags:

Share This Post:

Related Post

Stay Connected

Popular

Hot Categories

Tags

About Us

Instagram

Contact Info