6.3 User-defined Functions

User-Defined Functions (UDFs) allow you to create your own custom functions in SQL Server. There are two main types of UDFs: Scalar Functions and Table-Valued Functions.

6.1 Scalar Functions

Scalar functions return a single value. Here's how you can create and use a scalar function:

-- Creating a Scalar Function
CREATE FUNCTION dbo.AddNumbers
(
    @num1 INT,
    @num2 INT
)
RETURNS INT
AS
BEGIN
    DECLARE @result INT;
    SET @result = @num1 + @num2;
    RETURN @result;
END;
-- Using the Scalar Function
SELECT dbo.AddNumbers(5, 3) AS SumResult;

SumResult
8

In this example, we created a scalar function named AddNumbers that takes two parameters (@num1 and @num2) and returns their sum. The query output in the table shows the result of calling this function with the arguments 5 and 3.

6.2 Table-Valued Functions

Table-Valued Functions return a table as a result. Here's an example:

-- Creating a Table-Valued Function
CREATE FUNCTION dbo.GetEmployeeDetails
(
    @departmentID INT
)
RETURNS TABLE
AS
RETURN
(
    SELECT EmployeeID, FirstName, LastName
    FROM Employees
    WHERE DepartmentID = @departmentID
);
-- Using the Table-Valued Function
SELECT * FROM dbo.GetEmployeeDetails(1);

EmployeeID	FirstName	LastName
1	John	Doe
2	Jane	Smith
3	Bob	Johnson

In this example, we created a table-valued function named GetEmployeeDetails that takes a @departmentID parameter and returns employee details for that department. The query output in the table displays the result of calling this function with the argument 1.

These examples illustrate the basic concepts of creating and using both scalar and table-valued functions in SQL Server. Feel free to explore more advanced scenarios and functionalities as needed.