Home / SQL / SQL Server / T-SQL / SQL Server Views, Stored Procedures, and User-Defined Functions: A Complete Beginner's Guide

SQL Server Views, Stored Procedures, and User-Defined Functions: A Complete Beginner's Guide

On

Understanding views, stored procedures, and user-defined functions is crucial for every SQL developer who wants to write reusable, maintainable, and efficient database code. These three database objects—views, stored procedures, and user-defined functions—allow you to encapsulate complex logic, simplify queries, and create building blocks that make your database applications more organized and performant. In this comprehensive guide, I'll explain views, stored procedures, and user-defined functions using practical examples from the WideWorldImporters database, helping you understand when and how to use each one effectively.

As your SQL skills advance beyond basic queries, you'll discover that well-designed database objects dramatically improve code reusability, security, and maintainability. Learning to create and use views, stored procedures, and functions is a key milestone in becoming a professional database developer.

Understanding SQL Views

A view is a virtual table based on a SELECT query. Unlike a regular table, a view doesn't store data—it's essentially a saved query that you can reference like a table.

Developer Insight: I think of views as "saved queries with names." Instead of writing the same complex join repeatedly, create a view once and query it like a table. This approach has saved me countless hours and reduced errors significantly.

Why Use Views?

1. Simplify Complex Queries - Encapsulate joins and calculations

2. Security - Control which columns users can see

3. Consistency - Ensure everyone uses the same business logic

4. Abstraction - Hide underlying table structure changes

Creating a Basic View

-- View showing customer order summary
CREATE VIEW Sales.vw_CustomerOrderSummary
AS
SELECT 
    c.CustomerID,
    c.CustomerName,
    c.PhoneNumber,
    COUNT(o.OrderID) AS TotalOrders,
    MIN(o.OrderDate) AS FirstOrderDate,
    MAX(o.OrderDate) AS LastOrderDate
FROM Sales.Customers c
LEFT JOIN Sales.Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.CustomerName, c.PhoneNumber;

Now query the view like a table:

-- Query the view just like a table
SELECT CustomerName, TotalOrders, LastOrderDate
FROM Sales.vw_CustomerOrderSummary
WHERE TotalOrders > 10
ORDER BY TotalOrders DESC;

Complex View with Multiple Tables

-- View combining customers, orders, and products
CREATE VIEW Sales.vw_OrderDetails
AS
SELECT 
    o.OrderID,
    o.OrderDate,
    c.CustomerName,
    c.PhoneNumber,
    si.StockItemName,
    ol.Quantity,
    ol.UnitPrice,
    (ol.Quantity * ol.UnitPrice) AS LineTotal
FROM Sales.Orders o
INNER JOIN Sales.Customers c ON o.CustomerID = c.CustomerID
INNER JOIN Sales.OrderLines ol ON o.OrderID = ol.OrderID
INNER JOIN Warehouse.StockItems si ON ol.StockItemID = si.StockItemID;

Use the view to simplify complex queries:

-- Find high-value orders from the view
SELECT 
    OrderID,
    OrderDate,
    CustomerName,
    SUM(LineTotal) AS OrderTotal
FROM Sales.vw_OrderDetails
WHERE OrderDate >= '2016-01-01'
GROUP BY OrderID, OrderDate, CustomerName
HAVING SUM(LineTotal) > 5000
ORDER BY OrderTotal DESC;

Developer Insight: Views are perfect for reports that join multiple tables. Create the view once, let report developers query it without needing to understand complex joins. This separation of concerns is crucial in enterprise environments.

Modifying and Dropping Views

-- Modify an existing view
ALTER VIEW Sales.vw_CustomerOrderSummary
AS
SELECT 
    c.CustomerID,
    c.CustomerName,
    c.PhoneNumber,
    c.WebsiteURL,  -- Added column
    COUNT(o.OrderID) AS TotalOrders,
    SUM(ol.Quantity * ol.UnitPrice) AS TotalRevenue  -- Added calculation
FROM Sales.Customers c
LEFT JOIN Sales.Orders o ON c.CustomerID = o.CustomerID
LEFT JOIN Sales.OrderLines ol ON o.OrderID = ol.OrderID
GROUP BY c.CustomerID, c.CustomerName, c.PhoneNumber, c.WebsiteURL;

-- Drop a view when no longer needed
DROP VIEW IF EXISTS Sales.vw_CustomerOrderSummary;

Understanding Stored Procedures

A stored procedure is a precompiled collection of SQL statements saved in the database that can accept parameters, perform operations, and return results. Think of stored procedures as "database programs" that encapsulate business logic.

Why Use Stored Procedures?

1. Performance - Precompiled execution plans

2. Security - Grant execute permission without table access

3. Encapsulation - Business logic centralized in database

4. Reusability - Write once, execute from many applications

Creating a Simple Stored Procedure

-- Procedure to get customer details by ID
CREATE PROCEDURE Sales.usp_GetCustomerByID
    @CustomerID INT
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT 
        CustomerID,
        CustomerName,
        PhoneNumber,
        FaxNumber,
        WebsiteURL,
        DeliveryCityID
    FROM Sales.Customers
    WHERE CustomerID = @CustomerID;
END;

Execute the stored procedure:

-- Execute with parameter
EXEC Sales.usp_GetCustomerByID @CustomerID = 1;

-- Alternative syntax
EXECUTE Sales.usp_GetCustomerByID 1;

Stored Procedure with Multiple Parameters

-- Procedure to search orders by date range and customer
CREATE PROCEDURE Sales.usp_GetOrdersByDateAndCustomer
    @StartDate DATE,
    @EndDate DATE,
    @CustomerID INT = NULL  -- Optional parameter (default NULL)
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT 
        o.OrderID,
        o.OrderDate,
        o.ExpectedDeliveryDate,
        c.CustomerName,
        SUM(ol.Quantity * ol.UnitPrice) AS OrderTotal
    FROM Sales.Orders o
    INNER JOIN Sales.Customers c ON o.CustomerID = c.CustomerID
    INNER JOIN Sales.OrderLines ol ON o.OrderID = ol.OrderID
    WHERE o.OrderDate BETWEEN @StartDate AND @EndDate
      AND (@CustomerID IS NULL OR o.CustomerID = @CustomerID)
    GROUP BY o.OrderID, o.OrderDate, o.ExpectedDeliveryDate, c.CustomerName
    ORDER BY o.OrderDate DESC;
END;

Execute with different parameter combinations:

-- All customers in date range
EXEC Sales.usp_GetOrdersByDateAndCustomer 
    @StartDate = '2016-05-01',
    @EndDate = '2016-05-31';

-- Specific customer in date range
EXEC Sales.usp_GetOrdersByDateAndCustomer 
    @StartDate = '2016-01-01',
    @EndDate = '2016-12-31',
    @CustomerID = 832;

Stored Procedure with Output Parameters

-- Procedure that returns calculated values through output parameters
CREATE PROCEDURE Warehouse.usp_GetInventoryStats
    @SupplierID INT,
    @TotalItems INT OUTPUT,
    @TotalValue DECIMAL(18,2) OUTPUT,
    @LowStockCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT 
        @TotalItems = COUNT(DISTINCT si.StockItemID),
        @TotalValue = SUM(sih.QuantityOnHand * si.UnitPrice),
        @LowStockCount = SUM(CASE WHEN sih.QuantityOnHand < sih.ReorderLevel THEN 1 ELSE 0 END)
    FROM Warehouse.StockItems si
    INNER JOIN Warehouse.StockItemHoldings sih ON si.StockItemID = sih.StockItemID
    WHERE si.SupplierID = @SupplierID;
END;

Call with output parameters:

-- Declare variables to receive output
DECLARE @Items INT, @Value DECIMAL(18,2), @LowStock INT;

-- Execute procedure
EXEC Warehouse.usp_GetInventoryStats
    @SupplierID = 4,
    @TotalItems = @Items OUTPUT,
    @TotalValue = @Value OUTPUT,
    @LowStockCount = @LowStock OUTPUT;

-- Display results
SELECT @Items AS TotalItems, @Value AS TotalValue, @LowStock AS LowStockItems;

Developer Insight: Output parameters are excellent for returning status codes or aggregate values without needing to parse result sets. I use them frequently in ETL processes to capture row counts and error indicators.

Stored Procedure with Error Handling

-- Procedure with transaction and error handling
CREATE PROCEDURE Sales.usp_CreateCustomerOrder
    @CustomerID INT,
    @StockItemID INT,
    @Quantity INT
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- Validate inputs
        IF NOT EXISTS (SELECT 1 FROM Sales.Customers WHERE CustomerID = @CustomerID)
            THROW 50001, 'Customer does not exist', 1;
        
        IF NOT EXISTS (SELECT 1 FROM Warehouse.StockItems WHERE StockItemID = @StockItemID)
            THROW 50002, 'Stock item does not exist', 1;
        
        -- Create order (simplified example)
        DECLARE @OrderID INT;
        
        INSERT INTO Sales.Orders (CustomerID, OrderDate, ExpectedDeliveryDate)
        VALUES (@CustomerID, GETDATE(), DATEADD(DAY, 7, GETDATE()));
        
        SET @OrderID = SCOPE_IDENTITY();
        
        -- Add order line
        INSERT INTO Sales.OrderLines (OrderID, StockItemID, Quantity, UnitPrice)
        SELECT @OrderID, @StockItemID, @Quantity, UnitPrice
        FROM Warehouse.StockItems
        WHERE StockItemID = @StockItemID;
        
        COMMIT TRANSACTION;
        
        PRINT 'Order ' + CAST(@OrderID AS VARCHAR(10)) + ' created successfully';
        
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        
        DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
        DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
        
        RAISERROR(@ErrorMessage, @ErrorSeverity, 1);
    END CATCH;
END;

Understanding User-Defined Functions

User-defined functions (UDFs) are database objects that accept parameters, perform calculations or logic, and return a single value or table. Functions can be used within queries, unlike stored procedures.

Types of User-Defined Functions

1. Scalar Functions - Return a single value

2. Inline Table-Valued Functions - Return a table (one SELECT statement)

3. Multi-Statement Table-Valued Functions - Return a table (multiple statements)

Scalar Functions

-- Function to calculate order total
CREATE FUNCTION Sales.fn_CalculateOrderTotal (@OrderID INT)
RETURNS DECIMAL(18,2)
AS
BEGIN
    DECLARE @Total DECIMAL(18,2);
    
    SELECT @Total = SUM(Quantity * UnitPrice)
    FROM Sales.OrderLines
    WHERE OrderID = @OrderID;
    
    RETURN ISNULL(@Total, 0);
END;

Use the scalar function in queries:

-- Use function in SELECT
SELECT 
    OrderID,
    CustomerID,
    OrderDate,
    Sales.fn_CalculateOrderTotal(OrderID) AS OrderTotal
FROM Sales.Orders
WHERE OrderID <= 10
ORDER BY OrderID;

-- Use function in WHERE clause
SELECT OrderID, OrderDate
FROM Sales.Orders
WHERE Sales.fn_CalculateOrderTotal(OrderID) > 5000;

Inline Table-Valued Function

-- Function returning customers by city
CREATE FUNCTION Application.fn_GetCustomersByCity (@CityID INT)
RETURNS TABLE
AS
RETURN
(
    SELECT 
        CustomerID,
        CustomerName,
        PhoneNumber,
        WebsiteURL,
        DeliveryCityID
    FROM Sales.Customers
    WHERE DeliveryCityID = @CityID
);

Use the table-valued function:

-- Query the function like a table
SELECT CustomerID, CustomerName, PhoneNumber
FROM Application.fn_GetCustomersByCity(19586)
ORDER BY CustomerName;

-- Join to the function
SELECT 
    city.CityName,
    cust.CustomerName,
    cust.PhoneNumber
FROM Application.Cities city
CROSS APPLY Application.fn_GetCustomersByCity(city.CityID) cust
WHERE city.CityID IN (19586, 28077);

Developer Insight: Inline table-valued functions are my favorite type of function. They perform nearly as well as views but accept parameters, giving you the best of both worlds—performance and flexibility.

Multi-Statement Table-Valued Function

-- Function to get order summary with multiple steps
CREATE FUNCTION Sales.fn_GetOrderSummaryByCustomer (@CustomerID INT)
RETURNS @OrderSummary TABLE
(
    OrderID INT,
    OrderDate DATE,
    ItemCount INT,
    OrderTotal DECIMAL(18,2)
)
AS
BEGIN
    -- Insert order details
    INSERT INTO @OrderSummary
    SELECT 
        o.OrderID,
        o.OrderDate,
        COUNT(ol.OrderLineID) AS ItemCount,
        SUM(ol.Quantity * ol.UnitPrice) AS OrderTotal
    FROM Sales.Orders o
    INNER JOIN Sales.OrderLines ol ON o.OrderID = ol.OrderID
    WHERE o.CustomerID = @CustomerID
    GROUP BY o.OrderID, o.OrderDate;
    
    RETURN;
END;

Use the multi-statement function:

-- Query the function
SELECT OrderID, OrderDate, ItemCount, OrderTotal
FROM Sales.fn_GetOrderSummaryByCustomer(1)
ORDER BY OrderDate DESC;

Comparing Views, Stored Procedures, and Functions

Feature View Stored Procedure Scalar Function Table Function
Can be used in SELECT Yes No Yes Yes
Accepts parameters No Yes Yes Yes
Returns Table Result sets, output params Single value Table
Can modify data Limited Yes No No
Can use in WHERE Yes No Yes Yes
Can use in JOIN Yes No No Yes
Transaction control No Yes No No

When to Use Each:

Views - Simplify complex queries, security, read-only data access

Stored Procedures - Data modifications, complex business logic, transactions

Scalar Functions - Calculations used in queries, reusable formulas

Table Functions - Parameterized result sets, reusable filtered data

Developer Insight: Choose the simplest tool that solves your problem. Need a saved query? Use a view. Need parameters with that query? Use an inline table function. Need to modify data or handle transactions? Use a stored procedure.

Common Mistakes to Avoid

Mistake 1: Using Scalar Functions in WHERE Clauses on Large Tables

Problem: Scalar functions don't perform well when applied to many rows.

-- SLOW: Function runs for every row
SELECT OrderID, OrderDate
FROM Sales.Orders
WHERE Sales.fn_CalculateOrderTotal(OrderID) > 5000;

-- FASTER: Calculate in subquery or use inline function
SELECT o.OrderID, o.OrderDate
FROM Sales.Orders o
WHERE EXISTS (
    SELECT 1
    FROM Sales.OrderLines ol
    WHERE ol.OrderID = o.OrderID
    GROUP BY ol.OrderID
    HAVING SUM(ol.Quantity * ol.UnitPrice) > 5000
);

Mistake 2: Creating Views with SELECT *

Problem: View breaks when underlying table changes.

-- BAD: Uses SELECT *
CREATE VIEW Sales.vw_AllCustomers
AS
SELECT * FROM Sales.Customers;

-- GOOD: Explicitly list columns
CREATE VIEW Sales.vw_AllCustomers
AS
SELECT CustomerID, CustomerName, PhoneNumber, WebsiteURL
FROM Sales.Customers;

Mistake 3: Not Using SET NOCOUNT ON in Stored Procedures

Problem: Unnecessary "rows affected" messages create overhead.

-- Always include at start of stored procedures
CREATE PROCEDURE Sales.usp_GetOrders
AS
BEGIN
    SET NOCOUNT ON;  -- Prevents row count messages
    
    SELECT OrderID, CustomerID, OrderDate
    FROM Sales.Orders;
END;

Mistake 4: Multi-Statement Functions Instead of Inline Functions

Problem: Multi-statement functions perform poorly compared to inline.

-- SLOWER: Multi-statement function
CREATE FUNCTION dbo.fn_GetOrdersBad (@CustomerID INT)
RETURNS @Orders TABLE (OrderID INT, OrderDate DATE)
AS
BEGIN
    INSERT INTO @Orders
    SELECT OrderID, OrderDate
    FROM Sales.Orders
    WHERE CustomerID = @CustomerID;
    RETURN;
END;

-- FASTER: Inline function
CREATE FUNCTION dbo.fn_GetOrdersGood (@CustomerID INT)
RETURNS TABLE
AS
RETURN
(
    SELECT OrderID, OrderDate
    FROM Sales.Orders
    WHERE CustomerID = @CustomerID
);

Mistake 5: Not Handling NULLs in Functions

Problem: Functions return unexpected NULLs.

-- BAD: Returns NULL if no orders exist
CREATE FUNCTION Sales.fn_GetOrderCount (@CustomerID INT)
RETURNS INT
AS
BEGIN
    DECLARE @Count INT;
    SELECT @Count = COUNT(*) FROM Sales.Orders WHERE CustomerID = @CustomerID;
    RETURN @Count;
END;

-- GOOD: Use ISNULL or COALESCE
CREATE FUNCTION Sales.fn_GetOrderCount (@CustomerID INT)
RETURNS INT
AS
BEGIN
    RETURN ISNULL((SELECT COUNT(*) FROM Sales.Orders WHERE CustomerID = @CustomerID), 0);
END;

Best Practices

For Views:

  1. Use descriptive names with vw_ prefix
  2. Explicitly list columns (avoid SELECT *)
  3. Keep logic simple for better performance
  4. Document complex views with comments
  5. Consider indexed views for frequently-accessed aggregations

For Stored Procedures:

  1. Use usp_ prefix for user stored procedures
  2. Always include SET NOCOUNT ON
  3. Implement proper error handling with TRY-CATCH
  4. Use transactions for multi-statement operations
  5. Document parameters and behavior

For Functions:

  1. Prefer inline table functions over multi-statement
  2. Avoid scalar functions in WHERE clauses on large tables
  3. Handle NULL inputs appropriately
  4. Keep functions simple and focused
  5. Use functions for true calculations, not data access

Summary and Key Takeaways

Mastering views, stored procedures, and user-defined functions is essential for professional SQL development. Here's what you need to remember:

Views are virtual tables based on saved SELECT queries:

  • Simplify complex joins and calculations
  • Provide security through column-level access control
  • No parameters, read-only by default
  • Query them like regular tables

Stored Procedures are precompiled SQL programs:

  • Accept input and output parameters
  • Encapsulate business logic and data modifications
  • Support transactions and error handling
  • Cannot be used in SELECT statements
  • Ideal for INSERT, UPDATE, DELETE operations

User-Defined Functions return values or tables:

  • Scalar functions: Return single values, usable in SELECT
  • Inline table functions: Return tables with one query (fast)
  • Multi-statement functions: Return tables with multiple steps (slower)
  • Cannot modify data or use transactions
  • Perfect for calculations and filtered result sets

Choosing the Right Tool:

  • Saved query without parameters → View
  • Saved query with parameters → Inline table function
  • Data modifications or transactions → Stored procedure
  • Reusable calculation → Scalar function

Developer Insight: The WideWorldImporters database contains excellent examples of views, procedures, and functions. Explore them using system views to understand professional implementation patterns. Good database design uses these objects to create clean separation between data access, business logic, and presentation layers.

Next Steps: Practice creating views, stored procedures, and functions in WideWorldImporters. Start simple, then gradually add complexity. Study the execution plans to understand performance implications. The ability to choose the right database object for each scenario is a key skill that distinguishes professional SQL developers.

Frequently Asked Questions

Q: What's the main difference between a view and a stored procedure?

A: A view is a saved SELECT query that you can treat like a table—you can query it, join to it, and use it in WHERE clauses. A stored procedure is a program that accepts parameters and can perform any SQL operations including data modifications. You execute procedures with EXEC command, while you SELECT from views. Use views for simplifying queries and providing read access; use stored procedures for complex operations, data modifications, and business logic requiring parameters or transactions.

Q: Can I use a function in a WHERE clause but not a stored procedure?

A: Correct. User-defined functions (both scalar and table-valued) can be used in SELECT, WHERE, JOIN, and other query clauses just like built-in functions. Stored procedures cannot be used within queries—they must be executed with EXEC command and return result sets separately. This is why functions are better for calculations used within queries, while stored procedures are better for complete operations like "process this order" or "generate this report."

Q: Should I use an inline table function or a multi-statement table function?

A: Almost always use inline table-valued functions. They perform much better because SQL Server can optimize them like views—incorporating them directly into the query plan. Multi-statement functions create a table variable, populate it, and return it, which prevents optimization and often results in poor performance. Use multi-statement functions only when you absolutely need multiple steps with variables and control flow logic that cannot be expressed in a single SELECT statement.

Tags: