Prev Next

User-Defined Functions (UDFs)

User-Defined Functions (UDFs) are functions created by the user to perform specific operations. They can return a single value or a table and are useful for encapsulating logic that can be reused in SQL queries.

Types of User-Defined Functions

  • Scalar Functions: Returns a single value of a specific data type.
  • Table-Valued Functions (TVF): Returns a table as a result.

Creating a Scalar Function

Example: Simple Scalar Function

CREATE FUNCTION GetFullName (@FirstName VARCHAR(50), @LastName VARCHAR(50))
RETURNS VARCHAR(100)
AS
BEGIN
    RETURN @FirstName + ' ' + @LastName;
END;

Explanation: This scalar function, GetFullName, concatenates the first and last names and returns the full name.

Using the Scalar Function

SELECT dbo.GetFullName('Mahatma', 'Gandhi') AS FullName;

Output:

FullName: Mahatma Gandhi

Creating a Table-Valued Function

Example: Inline Table-Valued Function

CREATE FUNCTION GetFreedomFightersByCity (@City VARCHAR(50))
RETURNS TABLE
AS
RETURN (
    SELECT FighterID, Name, BirthDate
    FROM FreedomFighters
    WHERE City = @City
);

Using the Table-Valued Function

SELECT * FROM dbo.GetFreedomFightersByCity('Chennai');

Output:

FighterIDNameBirthDate
1Mahatma Gandhi1869-10-02

Do's and Don'ts

Do's

  • Use functions to encapsulate reusable logic.
  • Document the purpose and usage of your functions.
  • Use table-valued functions for operations that return result sets.

Don'ts

  • Don't use functions for operations that can be done efficiently with simple queries.
  • Don't perform data modifications (INSERT, UPDATE, DELETE) inside scalar functions.
  • Don't forget to consider the performance impact of using complex functions in large datasets.
Prev Next