User-Defined Functions

-- Scalar UDF: calculate the discounted price of a product.
-- Takes the original price and a discount percentage, returns the final price.
-- Once created, call it anywhere a value expression is valid.
CREATE FUNCTION dbo.CalculateDiscountedPrice
(
    @OriginalPrice  DECIMAL(10,2),    -- input: the price before discount
    @DiscountPct    DECIMAL(5,2)      -- input: discount as a percentage (e.g. 15 for 15%)
)
RETURNS DECIMAL(10,2)                 -- the return type: a decimal number
WITH SCHEMABINDING                    -- prevent changes to referenced objects
AS
BEGIN
    DECLARE @DiscountedPrice DECIMAL(10,2);
    -- guard against invalid inputs
    IF @DiscountPct < 0 OR @DiscountPct > 100
        RETURN @OriginalPrice;        -- return unchanged price for invalid discount
    SET @DiscountedPrice = @OriginalPrice * (1 - @DiscountPct / 100.0);
    RETURN ROUND(@DiscountedPrice, 2);
END;
-- Calling the scalar UDF inside a SELECT:
SELECT
    Name,
    Price                                          AS OriginalPrice,
    dbo.CalculateDiscountedPrice(Price, 15)        AS Price_15PctOff,
    dbo.CalculateDiscountedPrice(Price, 25)        AS Price_25PctOff
FROM Products
WHERE Category = 'Electronics'
ORDER BY Price DESC;

-- Scalar UDF: format a full name consistently.
-- Handles NULL first or last name gracefully.
CREATE FUNCTION dbo.FormatFullName
(
    @FirstName  NVARCHAR(50),
    @LastName   NVARCHAR(50)
)
RETURNS NVARCHAR(101)
WITH SCHEMABINDING
AS
BEGIN
    RETURN
        CASE
            WHEN @FirstName IS NULL AND @LastName IS NULL THEN 'Unknown'
            WHEN @FirstName IS NULL THEN @LastName
            WHEN @LastName  IS NULL THEN @FirstName
            ELSE @LastName + ', ' + @FirstName   -- format: "Chen, Sarah"
        END;
END;
-- Scalar UDF: calculate age in years from a date of birth.
CREATE FUNCTION dbo.CalculateAge
(
    @DateOfBirth  DATE,
    @AsOfDate     DATE        -- pass the reference date explicitly -- no GETDATE() in UDF
)
RETURNS INT
AS
BEGIN
    RETURN
        DATEDIFF(YEAR, @DateOfBirth, @AsOfDate)
        - CASE
            WHEN MONTH(@DateOfBirth) > MONTH(@AsOfDate) THEN 1
            WHEN MONTH(@DateOfBirth) = MONTH(@AsOfDate)
             AND DAY(@DateOfBirth)   > DAY(@AsOfDate)  THEN 1
            ELSE 0
          END;
END;
-- Using both UDFs together in a query:
SELECT
    dbo.FormatFullName(u.FirstName, u.LastName)  AS CustomerName,
    u.Email,
    dbo.CalculateAge(u.DateOfBirth, CAST(GETDATE() AS DATE)) AS Age
FROM Users u
WHERE u.Country = 'United States'
ORDER BY Age DESC;

-- Inline TVF: return all orders for a given customer within a date range.
-- The function acts like a parameterised view -- no BEGIN/END, no RETURN variable.
CREATE FUNCTION dbo.GetCustomerOrders
(
    @UserId    INT,
    @StartDate DATE,
    @EndDate   DATE
)
RETURNS TABLE                          -- no explicit column list needed
WITH SCHEMABINDING
AS
RETURN                                 -- single SELECT, no BEGIN/END
(
    SELECT
        o.OrderId,
        o.OrderDate,
        o.Total,
        o.Status,
        COUNT(oi.OrderItemId)               AS ItemCount,
        SUM(oi.Quantity * oi.Price)         AS CalculatedTotal
    FROM dbo.Orders     o
    JOIN dbo.OrderItems oi ON o.OrderId = oi.OrderId
    WHERE o.UserId    = @UserId
      AND o.OrderDate BETWEEN @StartDate AND @EndDate
    GROUP BY o.OrderId, o.OrderDate, o.Total, o.Status
);
-- Calling an inline TVF using CROSS APPLY or direct FROM:
SELECT
    u.FirstName + ' ' + u.LastName  AS CustomerName,
    co.OrderId,
    co.OrderDate,
    co.ItemCount,
    co.CalculatedTotal
FROM dbo.Users u
CROSS APPLY dbo.GetCustomerOrders(u.UserId, '2024-01-01', '2024-06-30') co
WHERE u.Country = 'United States'
ORDER BY co.OrderDate DESC;

-- Multi-statement TVF: classify each customer by spending tier.
-- The conditional classification logic cannot be written as a single SELECT
-- without repetition, making an msTVF a reasonable choice here.
CREATE FUNCTION dbo.GetCustomerTiers
(
    @MinOrders  INT   -- only return customers with at least this many orders
)
RETURNS @Results TABLE               -- explicitly declare the return table structure
(
    UserId       INT,
    CustomerName NVARCHAR(101),
    TotalOrders  INT,
    TotalSpent   DECIMAL(10,2),
    SpendTier    NVARCHAR(20)
)
AS
BEGIN
    -- first populate base spend data into the return table
    INSERT INTO @Results (UserId, CustomerName, TotalOrders, TotalSpent, SpendTier)
    SELECT
        u.UserId,
        u.FirstName + ' ' + u.LastName,
        COUNT(o.OrderId),
        SUM(o.Total),
        'Unclassified'              -- placeholder -- updated in the next step
    FROM dbo.Users  u
    JOIN dbo.Orders o ON u.UserId = o.UserId
    GROUP BY u.UserId, u.FirstName, u.LastName
    HAVING COUNT(o.OrderId) >= @MinOrders;
    -- then apply the tier classification based on total spend
    UPDATE @Results
    SET SpendTier =
        CASE
            WHEN TotalSpent >= 1500 THEN 'Platinum'
            WHEN TotalSpent >= 750  THEN 'Gold'
            WHEN TotalSpent >= 300  THEN 'Silver'
            ELSE                         'Bronze'
        END;
    RETURN;         -- return the fully populated @Results table
END;
-- Calling the msTVF:
SELECT SpendTier, COUNT(*) AS Customers, SUM(TotalSpent) AS TierRevenue
FROM dbo.GetCustomerTiers(3)       -- only customers with 3 or more orders
GROUP BY SpendTier
ORDER BY TierRevenue DESC;

-- Safe create-or-replace pattern for UDFs.
-- Drop if exists, then recreate -- preserves a clean definition each deployment.
-- Check if the function exists and drop it if so
IF OBJECT_ID(N'dbo.CalculateDiscountedPrice', N'FN') IS NOT NULL
    DROP FUNCTION dbo.CalculateDiscountedPrice;
GO
-- Recreate with updated logic (now applies a minimum floor price)
CREATE FUNCTION dbo.CalculateDiscountedPrice
(
    @OriginalPrice  DECIMAL(10,2),
    @DiscountPct    DECIMAL(5,2)
)
RETURNS DECIMAL(10,2)
WITH SCHEMABINDING
AS
BEGIN
    IF @DiscountPct < 0 OR @DiscountPct > 100
        RETURN @OriginalPrice;
    DECLARE @Discounted DECIMAL(10,2) =
        ROUND(@OriginalPrice * (1 - @DiscountPct / 100.0), 2);
    -- new rule: never go below $1.00 regardless of discount
    RETURN CASE WHEN @Discounted < 1.00 THEN 1.00 ELSE @Discounted END;
END;
GO
-- List all UDFs in the current database with their type and creation date
SELECT
    name                AS FunctionName,
    type_desc           AS FunctionType,      -- SCALAR_FUNCTION or TABLE_VALUED_FUNCTION
    create_date         AS Created,
    modify_date         AS LastModified
FROM sys.objects
WHERE type IN ('FN', 'IF', 'TF')              -- FN=scalar, IF=inline TVF, TF=multi-stmt TVF
ORDER BY name;