Comprehensive Guide to SQL Server User-Defined Functions Using an E-Commerce Database

 

Introduction

User-defined functions (UDFs) in SQL Server (T-SQL) are essential database objects that encapsulate reusable logic, returning scalar values or table result sets for use in queries, computations, or data transformations. Unlike stored procedures, UDFs integrate seamlessly into SELECT, WHERE, or JOIN clauses, making them ideal for modularizing calculations, formatting, or filtering in e-commerce applications. This guide uses the ECommerceDB database (11 tables, 20 rows each, as of September 8, 2025, 2:05 PM +06) to explore all SQL Server UDF types: Scalar Functions, Inline Table-Valued Functions (TVFs), and Multi-Statement Table-Valued Functions (MSTVFs). We cover creation, input parameters, execution (including storing outputs), error handling, real-world e-commerce scenarios, use cases, best practices, and common pitfalls. The content progresses through five complexity levels—Primary, Basic, Intermediate, Advanced, and Expert—each with examples for every UDF type. Additionally, 10 scenario-based assignment questions and answers are included for trainers to test applicants.

The ECommerceDB includes tables like Customers, Orders, Products, Order_Items, Payments, Sellers, Categories, Shipments, Reviews, Addresses, and Coupons, with constraints (e.g., PRIMARY KEY, FOREIGN KEY, CHECK) and sample data (e.g., 20 orders, total revenue 2728.00). Scenarios cover e-commerce tasks like discount calculations, order summaries, and inventory checks. Scripts are executable in SQL Server Management Studio (SSMS) and follow industry standards. Keywords include CREATE FUNCTION, @parameters, RETURNS, RETURN, TABLE, WITH SCHEMABINDING, and more. The content is SEO-optimized for terms like "T-SQL user-defined function tutorial," "SQL Server e-commerce examples," and "SQL function types." This inline guide aims to be exhaustive yet concise, covering all UDF types within each complexity level.

---

E-Commerce Database Context

The ECommerceDB models an e-commerce platform with 11 tables:

• Customers: User data (e.g., customer_id, username, email, is_active).
• Sellers: Vendor info (e.g., seller_id, business_name, rating).
• Categories: Product categories (e.g., category_id, name like 'Electronics').
• Products: Item details (e.g., product_id, name, price, stock).
• Orders: Purchases (e.g., order_id, customer_id, total_amount, status).
• Order_Items: Order-product links (e.g., item_id, order_id, quantity).
• Payments: Payment records (e.g., payment_id, order_id, method).
• Shipments: Shipping details (e.g., shipment_id, tracking_number).
• Reviews: Feedback (e.g., review_id, rating, comment).
• Addresses: Customer addresses (e.g., address_id, type, street).
• Coupons: Discounts (e.g., coupon_id, code, discount_percentage).

Each table has 20 rows with varied data (e.g., order dates in August 2025, statuses like 'Pending', 'Delivered'). Constraints ensure integrity (e.g., CHECK for rating BETWEEN 1 AND 5, UNIQUE for email). This supports scenarios like checkout processing, inventory management, and analytics.

---

Understanding SQL Server User-Defined Functions

SQL Server UDFs are categorized into three types:

1. Scalar Functions: Return a single value (e.g., calculate a discount).
2. Inline Table-Valued Functions (TVFs): Return a table result set via a single SELECT, optimized like views.
3. Multi-Statement Table-Valued Functions (MSTVFs): Return a table built programmatically with multiple T-SQL statements.

Benefits:

• Reusability: Encapsulate logic for use across queries.
• Integration: Embed in SELECT, WHERE, JOIN, or computed columns.
• Clarity: Simplify complex logic in queries.

Limitations:

• Cannot modify data (e.g., INSERT, UPDATE, DELETE) directly.
• Limited error handling (no TRY-CATCH in scalar or inline TVFs).
• MSTVFs may have performance overhead due to row-by-row processing.

Use Cases: UDFs are ideal for e-commerce tasks like formatting data, calculating totals, or filtering results. Pitfalls include overusing MSTVFs, neglecting input validation, and ignoring schema binding for optimization.

---

UDF Complexity Levels: Step-by-Step Examples

Each level includes examples for Scalar, Inline TVF, and MSTVF, with detailed explanations, execution, outputs, use cases, error handling, and best practices.

Primary Level: Basic Functions with Minimal Logic

Purpose: Introduce UDF creation and execution with simple logic, minimal or no parameters.

Scalar Function: Format Customer Initials

Scenario: Return a customer’s initials for display in compact UI elements.

sql

CREATE FUNCTION dbo.GetCustomerInitials
(
    @CustomerID INT
)
RETURNS VARCHAR(10)
AS
BEGIN
    DECLARE @Initials VARCHAR(10);
    SELECT @Initials = UPPER(LEFT(first_name, 1) + LEFT(last_name, 1))
    FROM Customers
    WHERE customer_id = @CustomerID;
    RETURN ISNULL(@Initials, '');
END;
GO

-- Execute in a query
SELECT customer_id, username, dbo.GetCustomerInitials(customer_id) AS Initials
FROM Customers
WHERE is_active = 1;

-- Store output in a variable
DECLARE @Initials VARCHAR(10);
SELECT @Initials = dbo.GetCustomerInitials(1);
SELECT @Initials AS CustomerInitials; -- Output: 'JD'

-- Test with invalid ID
SELECT dbo.GetCustomerInitials(999) AS Initials; -- Output: ''

Explanation:

• Purpose: Generates initials (e.g., 'JD' for John Doe) for user badges or reports.
• Code Breakdown:
  • @CustomerID: Input to identify customer.
  • RETURNS VARCHAR(10): Short string for initials.
  • UPPER(LEFT(...)): Extracts and capitalizes first letters.
  • ISNULL: Returns empty string for invalid IDs.
• Execution: Use in SELECT or assign to a variable.
• Output: For customer_id = 1, Initials = 'JD'.
• Challenges: Handling NULL names, invalid IDs.
• Use Case: Compact UI displays, email signatures.
• Error Handling: Returns empty string for invalid inputs.
• Best Practices: Use ISNULL, keep logic simple, ensure deterministic output.

Inline TVF: List Active Categories

Scenario: Retrieve active categories for navigation menus.

sql

CREATE FUNCTION dbo.GetActiveCategories
()
RETURNS TABLE
AS
RETURN
(
    SELECT category_id, name
    FROM Categories
    WHERE is_active = 1
);
GO

-- Execute in a query
SELECT * FROM dbo.GetActiveCategories();

-- Store output in a table
CREATE TABLE #ActiveCategories (
    category_id INT,
    name VARCHAR(50)
);
INSERT INTO #ActiveCategories
SELECT * FROM dbo.GetActiveCategories();
SELECT * FROM #ActiveCategories; -- Output: 20 rows, e.g., (1, 'Electronics')
DROP TABLE #ActiveCategories;

Explanation:

• Purpose: Lists active categories for website navigation.
• Code Breakdown:
  • RETURNS TABLE: Defines table output.
  • RETURN (SELECT ...): Filters active categories.
• Execution: Use in SELECT or store in a temporary table.
• Output: 20 rows (all categories active in sample data).
• Challenges: Handling empty results if no categories are active.
• Use Case: Category menus, product filtering.
• Error Handling: Returns empty table if no active categories.
• Best Practices: Use inline TVF for performance, simple SELECT.

MSTVF: Summarize Product Counts by Category

Scenario: Count products per category for inventory overview.

sql

CREATE FUNCTION dbo.GetProductCountsByCategory
()
RETURNS @ProductCounts TABLE
(
    CategoryID INT,
    CategoryName VARCHAR(50),
    ProductCount INT
)
AS
BEGIN
    INSERT INTO @ProductCounts
    SELECT 
        c.category_id,
        c.name,
        COUNT(p.product_id) AS ProductCount
    FROM Categories c
    LEFT JOIN Products p ON c.category_id = p.category_id
    GROUP BY c.category_id, c.name;
    RETURN;
END;
GO

-- Execute in a query
SELECT * FROM dbo.GetProductCountsByCategory();

-- Store output in a table
CREATE TABLE #CategoryCounts (
    CategoryID INT,
    CategoryName VARCHAR(50),
    ProductCount INT
);
INSERT INTO #CategoryCounts
SELECT * FROM dbo.GetProductCountsByCategory();
SELECT * FROM #CategoryCounts; -- Output: e.g., (1, 'Electronics', 1)
DROP TABLE #CategoryCounts;

Explanation:

• Purpose: Summarizes product counts for inventory reports.
• Code Breakdown:
  • RETURNS @ProductCounts TABLE: Defines output structure.
  • INSERT INTO @ProductCounts: Aggregates product counts.
  • LEFT JOIN: Includes categories with no products.
• Execution: Use in SELECT or store in a table.
• Output: 20 rows (e.g., 1, Electronics, 1).
• Challenges: Handling categories with zero products.
• Use Case: Inventory dashboards, category management.
• Error Handling: Empty table for no data.
• Best Practices: Use LEFT JOIN for inclusivity, clear table definition.

---

Basic Level: Functions with Input Parameters

Purpose: Add input parameters for dynamic logic.

Scalar Function: Calculate Order Tax

Scenario: Compute tax for an order amount based on a given rate.

sql

CREATE FUNCTION dbo.CalculateOrderTax
(
    @Amount DECIMAL(10,2),
    @TaxRate DECIMAL(5,2)
)
RETURNS DECIMAL(10,2)
AS
BEGIN
    IF @Amount < 0 OR @TaxRate < 0 OR @TaxRate > 1
        RETURN 0.00;
    DECLARE @TaxAmount DECIMAL(10,2) = @Amount * @TaxRate;
    RETURN @TaxAmount;
END;
GO

-- Execute in a query
SELECT order_id, total_amount, dbo.CalculateOrderTax(total_amount, 0.08) AS TaxAmount
FROM Orders;

-- Store output in a variable
DECLARE @Tax DECIMAL(10,2);
SELECT @Tax = dbo.CalculateOrderTax(1200.00, 0.08);
SELECT @Tax AS TaxAmount; -- Output: 96.00

-- Test with invalid inputs
SELECT dbo.CalculateOrderTax(-100.00, 0.08) AS TaxAmount; -- Output: 0.00

Explanation:

• Purpose: Calculates tax for checkout summaries.
• Code Breakdown:
  • @Amount, @TaxRate: Inputs for amount and rate.
  • IF ...: Validates inputs.
  • RETURN @Amount * @TaxRate: Computes tax.
• Execution: Use in SELECT or variable.
• Output: For 1200.00, 0.08, TaxAmount = 96.00.
• Challenges: Handling negative or invalid rates.
• Use Case: Tax display in checkout.
• Error Handling: Returns 0.00 for invalid inputs.
• Best Practices: Validate inputs, use precise DECIMAL.

Inline TVF: Get Orders by Customer

Scenario: Retrieve orders for a specific customer.

sql

CREATE FUNCTION dbo.GetCustomerOrders
(
    @CustomerID INT
)
RETURNS TABLE
AS
RETURN
(
    SELECT order_id, order_date, total_amount, status
    FROM Orders
    WHERE customer_id = @CustomerID
);
GO

-- Execute in a query
SELECT * FROM dbo.GetCustomerOrders(1);

-- Store output in a table
CREATE TABLE #CustomerOrders (
    order_id INT,
    order_date DATETIME,
    total_amount DECIMAL(10,2),
    status VARCHAR(20)
);
INSERT INTO #CustomerOrders
SELECT * FROM dbo.GetCustomerOrders(1);
SELECT * FROM #CustomerOrders; -- Output: e.g., (1, '2025-08-01', 1200.00, 'Delivered')
DROP TABLE #CustomerOrders;

-- Test with invalid ID
SELECT * FROM dbo.GetCustomerOrders(999); -- Empty table

Explanation:

• Purpose: Lists customer orders for history pages.
• Code Breakdown:
  • @CustomerID: Filters orders.
  • RETURNS TABLE: Returns order details.
  • WHERE customer_id = @CustomerID: Filters by customer.
• Execution: Use in SELECT or table.
• Output: For customer_id = 1, 1 row (e.g., 1, 2025-08-01, 1200.00, Delivered).
• Challenges: Validating FOREIGN KEY.
• Use Case: Order history display.
• Error Handling: Empty table for invalid IDs.
• Best Practices: Inline TVF for performance.

MSTVF: Get Customer Recent Orders

Scenario: List a customer’s recent orders with item counts.

sql

CREATE FUNCTION dbo.GetCustomerRecentOrders
(
    @CustomerID INT,
    @MaxOrders INT
)
RETURNS @RecentOrders TABLE
(
    OrderID INT,
    OrderDate DATETIME,
    TotalAmount DECIMAL(10,2),
    ItemCount INT
)
AS
BEGIN
    INSERT INTO @RecentOrders
    SELECT TOP (@MaxOrders)
        o.order_id,
        o.order_date,
        o.total_amount,
        COUNT(oi.item_id) AS ItemCount
    FROM Orders o
    LEFT JOIN Order_Items oi ON o.order_id = oi.order_id
    WHERE o.customer_id = @CustomerID
    GROUP BY o.order_id, o.order_date, o.total_amount
    ORDER BY o.order_date DESC;
    RETURN;
END;
GO

-- Execute in a query
SELECT * FROM dbo.GetCustomerRecentOrders(1, 5);

-- Store output in a table
CREATE TABLE #RecentOrders (
    OrderID INT,
    OrderDate DATETIME,
    TotalAmount DECIMAL(10,2),
    ItemCount INT
);
INSERT INTO #RecentOrders
SELECT * FROM dbo.GetCustomerRecentOrders(1, 5);
SELECT * FROM #RecentOrders; -- Output: e.g., (1, '2025-08-01', 1200.00, 1)
DROP TABLE #RecentOrders;

Explanation:

• Purpose: Summarizes recent orders for customer dashboards.
• Code Breakdown:
  • @CustomerID, @MaxOrders: Inputs for filtering.
  • RETURNS @RecentOrders TABLE: Defines output.
  • TOP (@MaxOrders): Limits results.
  • LEFT JOIN: Includes orders with no items.
• Execution: Use in SELECT or table.
• Output: For customer_id = 1, 1 row.
• Challenges: Handling zero items, invalid @MaxOrders.
• Use Case: Recent order tracking.
• Error Handling: Empty table for invalid inputs.
• Best Practices: Clear table structure, validate @MaxOrders.

---

Intermediate Level: Functions with Complex Logic

Purpose: Incorporate joins, aggregations, or conditional logic.

Scalar Function: Calculate Discounted Order Total

Scenario: Compute order total after applying a coupon.

sql

CREATE FUNCTION dbo.CalculateDiscountedOrderTotal
(
    @OrderID INT,
    @CouponCode VARCHAR(20)
)
RETURNS DECIMAL(10,2)
AS
BEGIN
    DECLARE @TotalAmount DECIMAL(10,2);
    DECLARE @DiscountPercentage DECIMAL(5,2);
    
    SELECT @TotalAmount = SUM(subtotal)
    FROM Order_Items
    WHERE order_id = @OrderID;
    
    IF @TotalAmount IS NULL
        RETURN 0.00;
    
    SELECT @DiscountPercentage = discount_percentage
    FROM Coupons
    WHERE code = @CouponCode
    AND is_active = 1
    AND GETDATE() BETWEEN start_date AND end_date;
    
    IF @DiscountPercentage IS NOT NULL
        SET @TotalAmount = @TotalAmount * (1 - @DiscountPercentage / 100);
    
    RETURN @TotalAmount;
END;
GO

-- Execute in a query
SELECT order_id, total_amount, dbo.CalculateDiscountedOrderTotal(order_id, 'SUMMER15') AS DiscountedTotal
FROM Orders;

-- Store output in a variable
DECLARE @DiscountedTotal DECIMAL(10,2);
SELECT @DiscountedTotal = dbo.CalculateDiscountedOrderTotal(1, 'SUMMER15');
SELECT @DiscountedTotal AS DiscountedTotal; -- Output: 1020.00

Explanation:

• Purpose: Applies coupon discount to order total.
• Code Breakdown:
  • @OrderID, @CouponCode: Inputs.
  • SELECT @TotalAmount: Sums order items.
  • SELECT @DiscountPercentage: Gets valid coupon.
  • Applies discount if valid.
• Execution: Use in SELECT or variable.
• Output: For order_id = 1, SUMMER15 (15%), 1020.00.
• Challenges: Validating coupon, handling NULLs.
• Use Case: Checkout pricing.
• Error Handling: Returns 0.00 for invalid orders.
• Best Practices: Handle NULLs, validate inputs.

Inline TVF: Get Products by Seller and Category

Scenario: List products by seller and category.

sql

CREATE FUNCTION dbo.GetProductsBySellerAndCategory
(
    @SellerID INT,
    @CategoryID INT
)
RETURNS TABLE
AS
RETURN
(
    SELECT p.product_id, p.name, p.price, p.stock, s.business_name
    FROM Products p
    INNER JOIN Sellers s ON p.seller_id = s.seller_id
    INNER JOIN Categories c ON p.category_id = c.category_id
    WHERE p.seller_id = @SellerID
    AND p.category_id = @CategoryID
);
GO

-- Execute in a query
SELECT * FROM dbo.GetProductsBySellerAndCategory(1, 1);

-- Store output in a table
CREATE TABLE #SellerProducts (
    product_id INT,
    name VARCHAR(100),
    price DECIMAL(10,2),
    stock INT,
    business_name VARCHAR(100)
);
INSERT INTO #SellerProducts
SELECT * FROM dbo.GetProductsBySellerAndCategory(1, 1);
SELECT * FROM #SellerProducts; -- Output: e.g., (1, 'Laptop', 1200.00, 50, 'TechTrend')
DROP TABLE #SellerProducts;

Explanation:

• Purpose: Filters products for seller dashboards.
• Code Breakdown:
  • @SellerID, @CategoryID: Filter inputs.
  • INNER JOIN: Combines tables.
  • WHERE: Filters by seller and category.
• Execution: Use in SELECT or table.
• Output: For seller_id = 1, category_id = 1, 1 row.
• Challenges: Validating FKs.
• Use Case: Seller product management.
• Error Handling: Empty table for invalid inputs.
• Best Practices: Inline TVF for efficiency.

MSTVF: Get Order Details with Items

Scenario: Return order details with itemized breakdown.

sql

CREATE FUNCTION dbo.GetOrderDetailsWithItems
(
    @OrderID INT
)
RETURNS @OrderDetails TABLE
(
    OrderID INT,
    CustomerName VARCHAR(100),
    TotalAmount DECIMAL(10,2),
    ProductName VARCHAR(100),
    Quantity INT,
    Subtotal DECIMAL(10,2)
)
AS
BEGIN
    INSERT INTO @OrderDetails
    SELECT 
        o.order_id,
        c.first_name + ' ' + c.last_name AS CustomerName,
        o.total_amount,
        p.name,
        oi.quantity,
        oi.subtotal
    FROM Orders o
    INNER JOIN Customers c ON o.customer_id = c.customer_id
    INNER JOIN Order_Items oi ON o.order_id = oi.order_id
    INNER JOIN Products p ON oi.product_id = p.product_id
    WHERE o.order_id = @OrderID;
    RETURN;
END;
GO

-- Execute in a query
SELECT * FROM dbo.GetOrderDetailsWithItems(1);

-- Store output in a table
CREATE TABLE #OrderDetails (
    OrderID INT,
    CustomerName VARCHAR(100),
    TotalAmount DECIMAL(10,2),
    ProductName VARCHAR(100),
    Quantity INT,
    Subtotal DECIMAL(10,2)
);
INSERT INTO #OrderDetails
SELECT * FROM dbo.GetOrderDetailsWithItems(1);
SELECT * FROM #OrderDetails; -- Output: e.g., (1, 'John Doe', 1200.00, 'Laptop', 1, 1200.00)
DROP TABLE #OrderDetails;

Explanation:

• Purpose: Detailed order view for invoices.
• Code Breakdown:
  • @OrderID: Filters order.
  • RETURNS @OrderDetails TABLE: Defines output.
  • INSERT: Joins multiple tables for details.
• Execution: Use in SELECT or table.
• Output: For order_id = 1, 1 row.
• Challenges: Multiple joins, NULL handling.
• Use Case: Order confirmation emails.
• Error Handling: Empty table for invalid IDs.
• Best Practices: Clear column definitions.

---

Advanced Level: Functions with Aggregations and Joins

Purpose: Handle complex calculations, joins, and schema binding.

Scalar Function: Calculate Average Product Rating

Scenario: Compute a product’s average rating with validation.

sql

CREATE FUNCTION dbo.CalculateAverageProductRating
(
    @ProductID INT
)
RETURNS FLOAT
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @AvgRating FLOAT;
    IF NOT EXISTS (SELECT 1 FROM dbo.Products WHERE product_id = @ProductID)
        RETURN 0.0;
    SELECT @AvgRating = AVG(CAST(rating AS FLOAT))
    FROM dbo.Reviews
    WHERE product_id = @ProductID;
    RETURN ISNULL(@AvgRating, 0.0);
END;
GO

-- Execute in a query
SELECT product_id, name, dbo.CalculateAverageProductRating(product_id) AS AvgRating
FROM Products;

-- Store output in a variable
DECLARE @AvgRating FLOAT;
SELECT @AvgRating = dbo.CalculateAverageProductRating(1);
SELECT @AvgRating AS AvgRating; -- Output: 5.0

Explanation:

• Purpose: Computes product rating for display.
• Code Breakdown:
  • @ProductID: Input for product.
  • WITH SCHEMABINDING: Optimizes performance.
  • IF NOT EXISTS: Validates product.
  • AVG(CAST(rating AS FLOAT)): Calculates average.
• Execution: Use in SELECT or variable.
• Output: For product_id = 1, AvgRating = 5.0.
• Challenges: Handling no reviews, schema binding.
• Use Case: Product pages, reviews.
• Error Handling: Returns 0.0 for invalid inputs.
• Best Practices: Use SCHEMABINDING, precise CAST.

Inline TVF: Get Top Customers by Spending

Scenario: List top customers by total spending.

sql

CREATE FUNCTION dbo.GetTopCustomersBySpending
(
    @TopN INT
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
    SELECT TOP (@TopN)
        c.customer_id,
        c.username,
        SUM(o.total_amount) AS TotalSpent
    FROM dbo.Customers c
    INNER JOIN dbo.Orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id, c.username
    ORDER BY TotalSpent DESC
);
GO

-- Execute in a query
SELECT * FROM dbo.GetTopCustomersBySpending(5);

-- Store output in a table
CREATE TABLE #TopCustomers (
    customer_id INT,
    username VARCHAR(50),
    TotalSpent DECIMAL(10,2)
);
INSERT INTO #TopCustomers
SELECT * FROM dbo.GetTopCustomersBySpending(5);
SELECT * FROM #TopCustomers; -- Output: e.g., (1, 'john_doe', 1200.00)
DROP TABLE #TopCustomers;

Explanation:

• Purpose: Identifies top spenders for marketing.
• Code Breakdown:
  • @TopN: Limits results.
  • WITH SCHEMABINDING: Optimizes.
  • TOP (@TopN): Dynamic limit.
  • SUM(o.total_amount): Aggregates spending.
• Execution: Use in SELECT or table.
• Output: For @TopN = 5, up to 5 rows.
• Challenges: Validating @TopN, handling no orders.
• Use Case: Loyalty program targeting.
• Error Handling: Empty table for invalid inputs.
• Best Practices: Use SCHEMABINDING, inline TVF.

MSTVF: Get Seller Sales by Category

Scenario: Summarize seller sales by category.

sql

CREATE FUNCTION dbo.GetSellerSalesByCategory
(
    @SellerID INT
)
RETURNS @Sales TABLE
(
    CategoryID INT,
    CategoryName VARCHAR(50),
    TotalSales DECIMAL(10,2),
    TotalItems INT
)
WITH SCHEMABINDING
AS
BEGIN
    INSERT INTO @Sales
    SELECT 
        c.category_id,
        c.name,
        SUM(oi.subtotal) AS TotalSales,
        SUM(oi.quantity) AS TotalItems
    FROM dbo.Categories c
    INNER JOIN dbo.Products p ON c.category_id = p.category_id
    INNER JOIN dbo.Order_Items oi ON p.product_id = oi.product_id
    WHERE p.seller_id = @SellerID
    GROUP BY c.category_id, c.name;
    RETURN;
END;
GO

-- Execute in a query
SELECT * FROM dbo.GetSellerSalesByCategory(1);

-- Store output in a table
CREATE TABLE #SellerSales (
    CategoryID INT,
    CategoryName VARCHAR(50),
    TotalSales DECIMAL(10,2),
    TotalItems INT
);
INSERT INTO #SellerSales
SELECT * FROM dbo.GetSellerSalesByCategory(1);
SELECT * FROM #SellerSales; -- Output: e.g., (1, 'Electronics', 1200.00, 1)
DROP TABLE #SellerSales;

Explanation:

• Purpose: Analyzes seller sales by category.
• Code Breakdown:
  • @SellerID: Filters seller.
  • RETURNS @Sales TABLE: Defines output.
  • WITH SCHEMABINDING: Optimizes.
  • SUM(oi.subtotal): Aggregates sales.
• Execution: Use in SELECT or table.
• Output: For seller_id = 1, 1 row.
• Challenges: Multiple joins, NULL handling.
• Use Case: Seller performance reports.
• Error Handling: Empty table for invalid IDs.
• Best Practices: Use SCHEMABINDING, clear structure.

---

Expert Level: Complex Functions with Robust Error Handling

Purpose: Combine complex logic, schema binding, and advanced validation.

Scalar Function: Calculate Final Order Total

Scenario: Compute final order total with discounts, taxes, and shipping.

sql

CREATE FUNCTION dbo.CalculateFinalOrderTotal
(
    @OrderID INT,
    @CouponCode VARCHAR(20)
)
RETURNS DECIMAL(10,2)
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @TotalAmount DECIMAL(10,2) = 0.00;
    DECLARE @DiscountPercentage DECIMAL(5,2);
    DECLARE @TaxRate DECIMAL(5,2) = 0.08;
    DECLARE @ShippingCost DECIMAL(10,2);
    
    IF NOT EXISTS (SELECT 1 FROM dbo.Orders WHERE order_id = @OrderID)
        RETURN 0.00;
    
    SELECT @TotalAmount = SUM(subtotal)
    FROM dbo.Order_Items
    WHERE order_id = @OrderID;
    
    IF @TotalAmount IS NULL
        RETURN 0.00;
    
    SELECT @DiscountPercentage = discount_percentage
    FROM dbo.Coupons
    WHERE code = @CouponCode
    AND is_active = 1
    AND GETDATE() BETWEEN start_date AND end_date;
    
    IF @DiscountPercentage IS NOT NULL
        SET @TotalAmount = @TotalAmount * (1 - @DiscountPercentage / 100);
    
    SET @ShippingCost = CASE 
        WHEN @TotalAmount >= 100.00 THEN 0.00
        ELSE 10.00
    END;
    
    SET @TotalAmount = @TotalAmount * (1 + @TaxRate) + @ShippingCost;
    
    RETURN @TotalAmount;
END;
GO

-- Execute in a query
SELECT order_id, total_amount, dbo.CalculateFinalOrderTotal(order_id, 'SUMMER15') AS FinalTotal
FROM Orders;

-- Store output in a variable
DECLARE @FinalTotal DECIMAL(10,2);
SELECT @FinalTotal = dbo.CalculateFinalOrderTotal(1, 'SUMMER15');
SELECT @FinalTotal AS FinalTotal; -- Output: 1101.60

Explanation:

• Purpose: Computes final checkout total.
• Code Breakdown:
  • @OrderID, @CouponCode: Inputs.
  • WITH SCHEMABINDING: Optimizes.
  • Validates order, calculates subtotal, applies discount, tax, and shipping.
• Execution: Use in SELECT or variable.
• Output: For order_id = 1, SUMMER15, 1101.60 (1200 * 0.85 * 1.08).
• Challenges: Complex calculations, schema binding.
• Use Case: Checkout finalization.
• Error Handling: Returns 0.00 for invalid inputs.
• Best Practices: Use SCHEMABINDING, validate inputs.

Inline TVF: Get Customer Purchase History

Scenario: Retrieve detailed purchase history for a customer.

sql

CREATE FUNCTION dbo.GetCustomerPurchaseHistory
(
    @CustomerID INT
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
    SELECT 
        o.order_id,
        o.order_date,
        p.name AS ProductName,
        oi.quantity,
        oi.subtotal
    FROM dbo.Orders o
    INNER JOIN dbo.Order_Items oi ON o.order_id = oi.order_id
    INNER JOIN dbo.Products p ON oi.product_id = p.product_id
    WHERE o.customer_id = @CustomerID
);
GO

-- Execute in a query
SELECT * FROM dbo.GetCustomerPurchaseHistory(1);

-- Store output in a table
CREATE TABLE #PurchaseHistory (
    order_id INT,
    order_date DATETIME,
    ProductName VARCHAR(100),
    quantity INT,
    subtotal DECIMAL(10,2)
);
INSERT INTO #PurchaseHistory
SELECT * FROM dbo.GetCustomerPurchaseHistory(1);
SELECT * FROM #PurchaseHistory; -- Output: e.g., (1, '2025-08-01', 'Laptop', 1, 1200.00)
DROP TABLE #PurchaseHistory;

Explanation:

• Purpose: Detailed purchase history for customer profiles.
• Code Breakdown:
  • @CustomerID: Filters customer.
  • WITH SCHEMABINDING: Optimizes.
  • Joins Orders, Order_Items, Products.
• Execution: Use in SELECT or table.
• Output: For customer_id = 1, 1 row.
• Challenges: Multiple joins, performance.
• Use Case: Customer account pages.
• Error Handling: Empty table for invalid IDs.
• Best Practices: Use inline TVF, SCHEMABINDING.

MSTVF: Get Product Sales Analysis

Scenario: Analyze product sales with ratings and categories.

sql

CREATE FUNCTION dbo.GetProductSalesAnalysis
(
    @MinSales DECIMAL(10,2)
)
RETURNS @SalesAnalysis TABLE
(
    ProductID INT,
    ProductName VARCHAR(100),
    CategoryName VARCHAR(50),
    TotalSales DECIMAL(10,2),
    AverageRating FLOAT
)
WITH SCHEMABINDING
AS
BEGIN
    INSERT INTO @SalesAnalysis
    SELECT 
        p.product_id,
        p.name,
        c.name AS CategoryName,
        SUM(oi.subtotal) AS TotalSales,
        AVG(CAST(r.rating AS FLOAT)) AS AverageRating
    FROM dbo.Products p
    INNER JOIN dbo.Categories c ON p.category_id = c.category_id
    INNER JOIN dbo.Order_Items oi ON p.product_id = oi.product_id
    LEFT JOIN dbo.Reviews r ON p.product_id = r.product_id
    GROUP BY p.product_id, p.name, c.name
    HAVING SUM(oi.subtotal) >= @MinSales;
    RETURN;
END;
GO

-- Execute in a query
SELECT * FROM dbo.GetProductSalesAnalysis(100.00);

-- Store output in a table
CREATE TABLE #SalesAnalysis (
    ProductID INT,
    ProductName VARCHAR(100),
    CategoryName VARCHAR(50),
    TotalSales DECIMAL(10,2),
    AverageRating FLOAT
);
INSERT INTO #SalesAnalysis
SELECT * FROM dbo.GetProductSalesAnalysis(100.00);
SELECT * FROM #SalesAnalysis; -- Output: e.g., (1, 'Laptop', 'Electronics', 1200.00, 5.0)
DROP TABLE #SalesAnalysis;

Explanation:

• Purpose: Analyzes product sales and ratings.
• Code Breakdown:
  • @MinSales: Filters by sales threshold.
  • RETURNS @SalesAnalysis TABLE: Defines output.
  • WITH SCHEMABINDING: Optimizes.
  • Aggregates sales and ratings.
• Execution: Use in SELECT or table.
• Output: For @MinSales = 100.00, 1 row.
• Challenges: Complex joins, NULL ratings.
• Use Case: Product performance reports.
• Error Handling: Empty table for no qualifying products.
• Best Practices: Use SCHEMABINDING, clear structure.

---

10 Scenario-Based Assignment Questions and Answers

These test all UDF types using ECommerceDB.

Question 1: Scalar - Calculate Shipping Cost

Scenario: Compute shipping cost based on order amount.

sql

CREATE FUNCTION dbo.CalculateShippingCost
(
    @OrderAmount DECIMAL(10,2)
)
RETURNS DECIMAL(10,2)
AS
BEGIN
    RETURN CASE 
        WHEN @OrderAmount >= 100.00 THEN 0.00
        ELSE 10.00
    END;
END;
GO

-- Execute
SELECT order_id, total_amount, dbo.CalculateShippingCost(total_amount) AS ShippingCost
FROM Orders; -- Output: e.g., (1, 1200.00, 0.00)

Explanation:

• Purpose: Determines shipping cost.
• Details: Uses CASE for logic.
• Use Case: Checkout cost display.
• Challenges: Negative amounts (validate externally).
• Best Practices: Simple logic, precise DECIMAL.

Question 2: Inline TVF - Get Active Coupons

Scenario: List active coupons.

sql

CREATE FUNCTION dbo.GetActiveCoupons
()
RETURNS TABLE
AS
RETURN
(
    SELECT code, discount_percentage
    FROM Coupons
    WHERE is_active = 1
    AND GETDATE() BETWEEN start_date AND end_date
);
GO

-- Execute
SELECT * FROM dbo.GetActiveCoupons(); -- Output: e.g., ('SUMMER15', 15.00)

Explanation:

• Purpose: Displays valid coupons.
• Details: Filters by is_active and date.
• Use Case: Coupon selection in checkout.
• Challenges: Date validation.
• Best Practices: Inline TVF, simple SELECT.

Question 3: MSTVF - Get Customer Order Stats

Scenario: Summarize customer order statistics.

sql

CREATE FUNCTION dbo.GetCustomerOrderStats
(
    @CustomerID INT
)
RETURNS @Stats TABLE
(
    CustomerID INT,
    TotalOrders INT,
    TotalSpent DECIMAL(10,2)
)
AS
BEGIN
    INSERT INTO @Stats
    SELECT 
        customer_id,
        COUNT(order_id),
        SUM(total_amount)
    FROM Orders
    WHERE customer_id = @CustomerID
    GROUP BY customer_id;
    RETURN;
END;
GO

-- Execute
SELECT * FROM dbo.GetCustomerOrderStats(1); -- Output: (1, 1, 1200.00)

Explanation:

• Purpose: Customer order summary.
• Details: Aggregates orders and spending.
• Use Case: Customer analytics.
• Challenges: No orders case.
• Best Practices: Clear table structure.

Question 4: Scalar - Format Address

Scenario: Format address for display.

sql

CREATE FUNCTION dbo.FormatAddress
(
    @AddressID INT
)
RETURNS VARCHAR(255)
AS
BEGIN
    DECLARE @Address VARCHAR(255);
    SELECT @Address = street + ', ' + city + ', ' + state + ' ' + zip_code
    FROM Addresses
    WHERE address_id = @AddressID;
    RETURN ISNULL(@Address, '');
END;
GO

-- Execute
SELECT address_id, dbo.FormatAddress(address_id) AS Address
FROM Addresses; -- Output: e.g., (1, '123 Main St, New York, NY 10001')

Explanation:

• Purpose: Formats address.
• Details: Concatenates fields.
• Use Case: Shipping labels.
• Challenges: NULL fields.
• Best Practices: Use ISNULL.

Question 5: Inline TVF - Get Product Reviews

Scenario: List reviews for a product.

sql

CREATE FUNCTION dbo.GetProductReviews
(
    @ProductID INT
)
RETURNS TABLE
AS
RETURN
(
    SELECT review_id, rating, comment
    FROM Reviews
    WHERE product_id = @ProductID
);
GO

-- Execute
SELECT * FROM dbo.GetProductReviews(1); -- Output: e.g., (1, 5, 'Great product!')

Explanation:

• Purpose: Displays product reviews.
• Details: Filters by product_id.
• Use Case: Product pages.
• Challenges: Empty results.
• Best Practices: Inline TVF for efficiency.

Question 6: MSTVF - Get Seller Product Stats

Scenario: Summarize seller product statistics.

sql

CREATE FUNCTION dbo.GetSellerProductStats
(
    @SellerID INT
)
RETURNS @Stats TABLE
(
    ProductID INT,
    ProductName VARCHAR(100),
    TotalSold INT,
    TotalRevenue DECIMAL(10,2)
)
AS
BEGIN
    INSERT INTO @Stats
    SELECT 
        p.product_id,
        p.name,
        SUM(oi.quantity),
        SUM(oi.subtotal)
    FROM Products p
    LEFT JOIN Order_Items oi ON p.product_id = oi.product_id
    WHERE p.seller_id = @SellerID
    GROUP BY p.product_id, p.name;
    RETURN;
END;
GO

-- Execute
SELECT * FROM dbo.GetSellerProductStats(1); -- Output: e.g., (1, 'Laptop', 1, 1200.00)

Explanation:

• Purpose: Seller product performance.
• Details: Aggregates sales data.
• Use Case: Seller dashboards.
• Challenges: No sales case.
• Best Practices: Clear table definition.

Question 7: Scalar - Calculate Coupon Savings

Scenario: Compute savings from a coupon.

sql

CREATE FUNCTION dbo.CalculateCouponSavings
(
    @OrderID INT,
    @CouponCode VARCHAR(20)
)
RETURNS DECIMAL(10,2)
AS
BEGIN
    DECLARE @Savings DECIMAL(10,2) = 0.00;
    DECLARE @DiscountPercentage DECIMAL(5,2);
    SELECT @DiscountPercentage = discount_percentage
    FROM Coupons
    WHERE code = @CouponCode
    AND is_active = 1
    AND GETDATE() BETWEEN start_date AND end_date;
    IF @DiscountPercentage IS NOT NULL
        SELECT @Savings = SUM(subtotal) * (@DiscountPercentage / 100)
        FROM Order_Items
        WHERE order_id = @OrderID;
    RETURN @Savings;
END;
GO

-- Execute
SELECT order_id, dbo.CalculateCouponSavings(order_id, 'SUMMER15') AS Savings
FROM Orders; -- Output: e.g., (1, 180.00)

Explanation:

• Purpose: Calculates coupon savings.
• Details: Applies discount to order items.
• Use Case: Checkout savings display.
• Challenges: Invalid coupons.
• Best Practices: Handle NULLs.

Question 8: Inline TVF - Get Orders by Date Range

Scenario: List orders within a date range.

sql

CREATE FUNCTION dbo.GetOrdersByDateRange
(
    @StartDate DATE,
    @EndDate DATE
)
RETURNS TABLE
AS
RETURN
(
    SELECT order_id, order_date, total_amount
    FROM Orders
    WHERE order_date BETWEEN @StartDate AND @EndDate
);
GO

-- Execute
SELECT * FROM dbo.GetOrdersByDateRange('2025-08-01', '2025-08-31');

Explanation:

• Purpose: Filters orders by date.
• Details: Uses BETWEEN for date range.
• Use Case: Sales reports.
• Challenges: Date validation.
• Best Practices: Inline TVF, simple filter.

Question 9: MSTVF - Get Customer Reviews

Scenario: Summarize customer reviews.

sql

CREATE FUNCTION dbo.GetCustomerReviews
(
    @CustomerID INT
)
RETURNS @Reviews TABLE
(
    ProductID INT,
    ProductName VARCHAR(100),
    Rating INT,
    Comment TEXT
)
AS
BEGIN
    INSERT INTO @Reviews
    SELECT 
        p.product_id,
        p.name,
        r.rating,
        r.comment
    FROM Reviews r
    INNER JOIN Products p ON r.product_id = p.product_id
    WHERE r.customer_id = @CustomerID;
    RETURN;
END;
GO

-- Execute
SELECT * FROM dbo.GetCustomerReviews(1); -- Output: e.g., (1, 'Laptop', 5, 'Great product!')

Explanation:

• Purpose: Customer review summary.
• Details: Joins Reviews and Products.
• Use Case: Customer feedback pages.
• Challenges: No reviews case.
• Best Practices: Clear table structure.

Question 10: Scalar - Calculate Seller Rating

Scenario: Compute seller’s average rating.

sql

CREATE FUNCTION dbo.CalculateSellerRating
(
    @SellerID INT
)
RETURNS FLOAT
AS
BEGIN
    DECLARE @AvgRating FLOAT;
    SELECT @AvgRating = AVG(CAST(r.rating AS FLOAT))
    FROM Products p
    INNER JOIN Reviews r ON p.product_id = r.product_id
    WHERE p.seller_id = @SellerID;
    RETURN ISNULL(@AvgRating, 0.0);
END;
GO

-- Execute
SELECT seller_id, business_name, dbo.CalculateSellerRating(seller_id) AS AvgRating
FROM Sellers; -- Output: e.g., (1, 'TechTrend', 5.0)

Explanation:

• Purpose: Seller rating calculation.
• Details: Averages product reviews.
• Use Case: Seller profiles.
• Challenges: No reviews case.
• Best Practices: Use ISNULL, CAST.

---

Best Practices and Standards

• Naming: Descriptive names (e.g., dbo.CalculateFinalOrderTotal).
• Schema Binding: Use WITH SCHEMABINDING for performance and integrity.
• Validation: Handle invalid inputs (e.g., return 0.00 or empty tables).
• Performance: Prefer inline TVFs over MSTVFs; avoid scalar functions in large queries.
• Error Handling: Use default values for invalid inputs; validate in calling queries.
• Determinism: Ensure deterministic output for indexing.
• Documentation: Comment code, document parameters.
• Testing: Test edge cases (e.g., invalid IDs, NULLs).
• Data Types: Match table column types.

---

Execution Notes

• Run ECommerceDB.sql to create the database.
• Execute functions in SSMS with USE ECommerceDB.
• Use in SELECT, WHERE, or JOIN clauses.
• Store outputs in variables or temporary tables.
• Verify functions in sys.objects (type IN ('FN', 'IF', 'TF')).

---

Conclusion

This guide covers all SQL Server UDF types—scalar, inline TVF, and MSTVF—using ECommerceDB with examples across five complexity levels. The 10 examples and assignments address e-commerce scenarios like pricing, order tracking, and seller analytics, ensuring practical learning. Best practices emphasize performance, validation, and clarity. Trainers can use assignments to test skills, while developers can apply these patterns to e-commerce systems. Explore further with table-valued parameters or computed columns.