Stored Procedures, Functions, and Triggers

Introduction

Stored procedures, functions, and triggers are database objects that encapsulate SQL logic for reuse, maintainability, and performance. Understanding when and how to use each is essential for database development.

Table of Contents

• Stored Procedures
• User-Defined Functions
• Triggers
• Comparison: Procedures vs Functions vs Triggers
• Best Practices
• Interview Questions

Stored Procedures

What is a Stored Procedure?

A stored procedure is a precompiled collection of SQL statements stored in the database that can be executed as a unit.

Basic Syntax

-- Create procedure
CREATE PROCEDURE usp_GetCustomerOrders
    @CustomerId INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        o.OrderId,
        o.OrderDate,
        o.TotalAmount,
        o.Status
    FROM Orders o
    WHERE o.CustomerId = @CustomerId
    ORDER BY o.OrderDate DESC;
END;

-- Execute procedure
EXEC usp_GetCustomerOrders @CustomerId = 123;
-- Or
EXECUTE usp_GetCustomerOrders 123;

Parameters

-- Input parameters (default)
CREATE PROCEDURE usp_SearchProducts
    @SearchTerm NVARCHAR(100),
    @CategoryId INT = NULL,        -- Optional with default
    @MinPrice DECIMAL(10,2) = 0,
    @MaxPrice DECIMAL(10,2) = 999999
AS
BEGIN
    SELECT ProductId, Name, Price
    FROM Products
    WHERE Name LIKE '%' + @SearchTerm + '%'
      AND (@CategoryId IS NULL OR CategoryId = @CategoryId)
      AND Price BETWEEN @MinPrice AND @MaxPrice;
END;

-- Output parameters
CREATE PROCEDURE usp_CreateOrder
    @CustomerId INT,
    @OrderDate DATE,
    @OrderId INT OUTPUT,
    @OrderNumber VARCHAR(20) OUTPUT
AS
BEGIN
    SET @OrderNumber = 'ORD-' + FORMAT(GETDATE(), 'yyyyMMdd') + '-' +
                       CAST(NEXT VALUE FOR OrderSequence AS VARCHAR);

    INSERT INTO Orders (CustomerId, OrderDate, OrderNumber)
    VALUES (@CustomerId, @OrderDate, @OrderNumber);

    SET @OrderId = SCOPE_IDENTITY();
END;

-- Calling with output parameters
DECLARE @NewOrderId INT, @NewOrderNumber VARCHAR(20);
EXEC usp_CreateOrder
    @CustomerId = 123,
    @OrderDate = '2024-01-15',
    @OrderId = @NewOrderId OUTPUT,
    @OrderNumber = @NewOrderNumber OUTPUT;

SELECT @NewOrderId AS OrderId, @NewOrderNumber AS OrderNumber;

Return Values

-- Return value for status codes
CREATE PROCEDURE usp_ProcessOrder
    @OrderId INT
AS
BEGIN
    IF NOT EXISTS (SELECT 1 FROM Orders WHERE OrderId = @OrderId)
        RETURN -1;  -- Order not found

    IF EXISTS (SELECT 1 FROM Orders WHERE OrderId = @OrderId AND Status = 'Completed')
        RETURN -2;  -- Already processed

    UPDATE Orders SET Status = 'Processing' WHERE OrderId = @OrderId;

    -- Process order logic here...

    UPDATE Orders SET Status = 'Completed' WHERE OrderId = @OrderId;
    RETURN 0;  -- Success
END;

-- Check return value
DECLARE @Result INT;
EXEC @Result = usp_ProcessOrder @OrderId = 123;

IF @Result = 0
    PRINT 'Order processed successfully';
ELSE IF @Result = -1
    PRINT 'Order not found';
ELSE IF @Result = -2
    PRINT 'Order already processed';

Error Handling

CREATE PROCEDURE usp_TransferFunds
    @FromAccountId INT,
    @ToAccountId INT,
    @Amount DECIMAL(18,2)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;  -- Auto-rollback on error

    BEGIN TRY
        BEGIN TRANSACTION;

        -- Validate source account
        DECLARE @CurrentBalance DECIMAL(18,2);
        SELECT @CurrentBalance = Balance
        FROM Accounts WITH (UPDLOCK)
        WHERE AccountId = @FromAccountId;

        IF @CurrentBalance IS NULL
            THROW 50001, 'Source account not found', 1;

        IF @CurrentBalance < @Amount
            THROW 50002, 'Insufficient funds', 1;

        -- Perform transfer
        UPDATE Accounts SET Balance = Balance - @Amount
        WHERE AccountId = @FromAccountId;

        UPDATE Accounts SET Balance = Balance + @Amount
        WHERE AccountId = @ToAccountId;

        -- Log transaction
        INSERT INTO TransactionLog (FromAccount, ToAccount, Amount, TransactionDate)
        VALUES (@FromAccountId, @ToAccountId, @Amount, GETDATE());

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        -- Re-throw with context
        DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
        DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
        DECLARE @ErrorState INT = ERROR_STATE();

        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH
END;

Calling from C# with ADO.NET

public async Task<List<Order>> GetCustomerOrdersAsync(int customerId)
{
    using var connection = new SqlConnection(_connectionString);
    using var command = new SqlCommand("usp_GetCustomerOrders", connection);

    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.AddWithValue("@CustomerId", customerId);

    await connection.OpenAsync();

    var orders = new List<Order>();
    using var reader = await command.ExecuteReaderAsync();

    while (await reader.ReadAsync())
    {
        orders.Add(new Order
        {
            OrderId = reader.GetInt32(0),
            OrderDate = reader.GetDateTime(1),
            TotalAmount = reader.GetDecimal(2),
            Status = reader.GetString(3)
        });
    }

    return orders;
}

// With output parameters
public async Task<(int OrderId, string OrderNumber)> CreateOrderAsync(int customerId)
{
    using var connection = new SqlConnection(_connectionString);
    using var command = new SqlCommand("usp_CreateOrder", connection);

    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.AddWithValue("@CustomerId", customerId);
    command.Parameters.AddWithValue("@OrderDate", DateTime.Today);

    var orderIdParam = command.Parameters.Add("@OrderId", SqlDbType.Int);
    orderIdParam.Direction = ParameterDirection.Output;

    var orderNumParam = command.Parameters.Add("@OrderNumber", SqlDbType.VarChar, 20);
    orderNumParam.Direction = ParameterDirection.Output;

    await connection.OpenAsync();
    await command.ExecuteNonQueryAsync();

    return ((int)orderIdParam.Value, (string)orderNumParam.Value);
}

Calling from EF Core

// Raw SQL execution
var orders = await _context.Orders
    .FromSqlRaw("EXEC usp_GetCustomerOrders @CustomerId = {0}", customerId)
    .ToListAsync();

// With SqlParameter for more control
var customerIdParam = new SqlParameter("@CustomerId", customerId);
var orders = await _context.Orders
    .FromSqlRaw("EXEC usp_GetCustomerOrders @CustomerId", customerIdParam)
    .ToListAsync();

// For non-query procedures
await _context.Database.ExecuteSqlRawAsync(
    "EXEC usp_UpdateInventory @ProductId = {0}, @Quantity = {1}",
    productId, quantity);

User-Defined Functions

Scalar Functions

Return a single value.

-- Create scalar function
CREATE FUNCTION fn_CalculateAge
(
    @BirthDate DATE
)
RETURNS INT
AS
BEGIN
    RETURN DATEDIFF(YEAR, @BirthDate, GETDATE()) -
           CASE
               WHEN DATEADD(YEAR, DATEDIFF(YEAR, @BirthDate, GETDATE()), @BirthDate) > GETDATE()
               THEN 1
               ELSE 0
           END;
END;

-- Usage
SELECT
    FirstName,
    LastName,
    dbo.fn_CalculateAge(BirthDate) AS Age
FROM Customers;

-- In WHERE clause
SELECT * FROM Customers
WHERE dbo.fn_CalculateAge(BirthDate) >= 18;

Table-Valued Functions (Inline)

Return a table from a single SELECT statement.

-- Inline TVF (best performance)
CREATE FUNCTION fn_GetCustomerOrders
(
    @CustomerId INT
)
RETURNS TABLE
AS
RETURN
(
    SELECT
        o.OrderId,
        o.OrderDate,
        o.TotalAmount,
        COUNT(oi.OrderItemId) AS ItemCount
    FROM Orders o
    LEFT JOIN OrderItems oi ON o.OrderId = oi.OrderId
    WHERE o.CustomerId = @CustomerId
    GROUP BY o.OrderId, o.OrderDate, o.TotalAmount
);

-- Usage (can be joined like a table)
SELECT
    c.CustomerName,
    o.OrderId,
    o.TotalAmount
FROM Customers c
CROSS APPLY dbo.fn_GetCustomerOrders(c.CustomerId) o
WHERE c.Region = 'East';

Table-Valued Functions (Multi-Statement)

Return a table variable with multiple statements.

-- Multi-statement TVF (more flexibility, less performance)
CREATE FUNCTION fn_GetOrderSummary
(
    @StartDate DATE,
    @EndDate DATE
)
RETURNS @Summary TABLE
(
    OrderDate DATE,
    OrderCount INT,
    TotalRevenue DECIMAL(18,2),
    AverageOrderValue DECIMAL(18,2)
)
AS
BEGIN
    INSERT INTO @Summary
    SELECT
        CAST(OrderDate AS DATE),
        COUNT(*),
        SUM(TotalAmount),
        AVG(TotalAmount)
    FROM Orders
    WHERE OrderDate BETWEEN @StartDate AND @EndDate
    GROUP BY CAST(OrderDate AS DATE);

    -- Can add more logic here
    UPDATE @Summary SET AverageOrderValue = 0 WHERE OrderCount = 0;

    RETURN;
END;

-- Usage
SELECT * FROM dbo.fn_GetOrderSummary('2024-01-01', '2024-12-31');

Deterministic vs Non-Deterministic

-- Deterministic: Same inputs always produce same output
CREATE FUNCTION fn_FullName(@First NVARCHAR(50), @Last NVARCHAR(50))
RETURNS NVARCHAR(101)
WITH SCHEMABINDING  -- Required for indexed views
AS
BEGIN
    RETURN @First + ' ' + @Last;
END;

-- Non-deterministic: Output can vary (can't use in indexed views)
CREATE FUNCTION fn_GetCurrentTime()
RETURNS DATETIME
AS
BEGIN
    RETURN GETDATE();  -- Changes every call
END;

Triggers

DML Triggers (INSERT, UPDATE, DELETE)

-- AFTER trigger (fires after the action)
CREATE TRIGGER tr_Orders_AfterInsert
ON Orders
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    -- Log new orders
    INSERT INTO OrderAuditLog (OrderId, Action, ActionDate, UserId)
    SELECT
        i.OrderId,
        'INSERT',
        GETDATE(),
        SYSTEM_USER
    FROM inserted i;

    -- Update inventory
    UPDATE p
    SET p.StockQuantity = p.StockQuantity - oi.Quantity
    FROM Products p
    INNER JOIN OrderItems oi ON p.ProductId = oi.ProductId
    INNER JOIN inserted i ON oi.OrderId = i.OrderId;
END;

-- INSTEAD OF trigger (replaces the action)
CREATE TRIGGER tr_Customers_InsteadOfDelete
ON Customers
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;

    -- Soft delete instead of hard delete
    UPDATE c
    SET c.IsDeleted = 1,
        c.DeletedDate = GETDATE()
    FROM Customers c
    INNER JOIN deleted d ON c.CustomerId = d.CustomerId;
END;

-- UPDATE trigger with column checking
CREATE TRIGGER tr_Products_PriceChange
ON Products
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    IF UPDATE(Price)  -- Only if Price column changed
    BEGIN
        INSERT INTO PriceHistory (ProductId, OldPrice, NewPrice, ChangeDate)
        SELECT
            i.ProductId,
            d.Price AS OldPrice,
            i.Price AS NewPrice,
            GETDATE()
        FROM inserted i
        INNER JOIN deleted d ON i.ProductId = d.ProductId
        WHERE i.Price <> d.Price;
    END
END;

Inserted and Deleted Tables

-- Both tables available in triggers
-- inserted: Contains new/updated rows
-- deleted: Contains old/deleted rows

CREATE TRIGGER tr_Orders_Audit
ON Orders
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Action VARCHAR(10);

    IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
        SET @Action = 'UPDATE';
    ELSE IF EXISTS (SELECT 1 FROM inserted)
        SET @Action = 'INSERT';
    ELSE
        SET @Action = 'DELETE';

    -- Log based on action
    IF @Action IN ('INSERT', 'UPDATE')
    BEGIN
        INSERT INTO OrderAuditLog (OrderId, Action, NewStatus, ActionDate)
        SELECT OrderId, @Action, Status, GETDATE() FROM inserted;
    END

    IF @Action IN ('DELETE', 'UPDATE')
    BEGIN
        INSERT INTO OrderAuditLog (OrderId, Action, OldStatus, ActionDate)
        SELECT OrderId, @Action, Status, GETDATE() FROM deleted;
    END
END;

DDL Triggers (Schema Changes)

-- Database-level trigger for schema changes
CREATE TRIGGER tr_PreventTableDrop
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @EventData XML = EVENTDATA();

    DECLARE @ObjectName NVARCHAR(256) =
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(256)');

    -- Prevent dropping important tables
    IF @ObjectName IN ('Customers', 'Orders', 'Products')
    BEGIN
        RAISERROR('Cannot drop or alter protected table: %s', 16, 1, @ObjectName);
        ROLLBACK;
    END

    -- Log schema changes
    INSERT INTO SchemaChangeLog (EventType, ObjectName, LoginName, EventDate, EventData)
    VALUES (
        @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),
        @ObjectName,
        @EventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(256)'),
        GETDATE(),
        @EventData
    );
END;

-- Server-level trigger
CREATE TRIGGER tr_LogonAudit
ON ALL SERVER
FOR LOGON
AS
BEGIN
    -- Log all logins (be careful with performance)
    INSERT INTO master.dbo.LogonAudit (LoginName, LoginTime, HostName)
    VALUES (ORIGINAL_LOGIN(), GETDATE(), HOST_NAME());
END;

Disabling and Enabling Triggers

-- Disable specific trigger
DISABLE TRIGGER tr_Orders_AfterInsert ON Orders;

-- Disable all triggers on table
DISABLE TRIGGER ALL ON Orders;

-- Enable trigger
ENABLE TRIGGER tr_Orders_AfterInsert ON Orders;

-- Disable during bulk operations
ALTER TABLE Orders DISABLE TRIGGER ALL;
-- Bulk insert here
ALTER TABLE Orders ENABLE TRIGGER ALL;

Comparison

Procedures vs Functions vs Triggers

*Scalar and inline TVFs cannot modify data; multi-statement TVFs have limitations.

When to Use Each

┌────────────────────────────────────────────────────────────────┐
│                      DECISION GUIDE                             │
├────────────────────────────────────────────────────────────────┤
│ Need to MODIFY data?                                           │
│   └── Yes → Stored Procedure or Trigger                        │
│                                                                 │
│ Need to use in SELECT/WHERE/JOIN?                              │
│   └── Yes → Function                                           │
│                                                                 │
│ Need automatic execution on data change?                       │
│   └── Yes → Trigger                                            │
│                                                                 │
│ Need to return multiple result sets?                           │
│   └── Yes → Stored Procedure                                   │
│                                                                 │
│ Need complex transaction control?                              │
│   └── Yes → Stored Procedure                                   │
│                                                                 │
│ Need reusable calculation in queries?                          │
│   └── Yes → Scalar or Inline TVF                               │
└────────────────────────────────────────────────────────────────┘

Best Practices

Stored Procedures

-- ✅ DO: Use SET NOCOUNT ON
CREATE PROCEDURE usp_Example AS
BEGIN
    SET NOCOUNT ON;  -- Prevents "n rows affected" messages
    -- ...
END;

-- ✅ DO: Use meaningful naming conventions
usp_GetCustomerById      -- usp_ prefix for user stored procedures
usp_CreateOrder
usp_UpdateInventory

-- ✅ DO: Validate parameters
IF @CustomerId IS NULL OR @CustomerId <= 0
    THROW 50000, 'Invalid CustomerId', 1;

-- ✅ DO: Use transactions for multiple statements
BEGIN TRANSACTION;
-- multiple operations
COMMIT TRANSACTION;

-- ❌ DON'T: Use dynamic SQL without parameterization
EXEC('SELECT * FROM Users WHERE Name = ''' + @Name + '''');  -- SQL Injection!

-- ✅ DO: Use sp_executesql for dynamic SQL
EXEC sp_executesql
    N'SELECT * FROM Users WHERE Name = @Name',
    N'@Name NVARCHAR(100)',
    @Name = @InputName;

Functions

-- ✅ DO: Keep functions deterministic when possible
-- ✅ DO: Use inline TVFs over multi-statement when possible (better performance)
-- ✅ DO: Use SCHEMABINDING for indexed view compatibility

-- ❌ DON'T: Put complex logic in scalar functions used in WHERE clauses
-- (Called once per row, poor performance)

-- ❌ DON'T: Access tables in scalar functions if avoidable
-- (Causes row-by-row execution)

Triggers

-- ✅ DO: Keep triggers short and fast
-- ✅ DO: Handle multi-row operations (inserted/deleted can have many rows)
-- ✅ DO: Include SET NOCOUNT ON
-- ✅ DO: Document trigger behavior

-- ❌ DON'T: Put business logic in triggers (hard to test/maintain)
-- ❌ DON'T: Call external resources (web services, file system)
-- ❌ DON'T: Create complex trigger chains (A triggers B triggers C)
-- ❌ DON'T: Use ROLLBACK without RAISERROR (silent failures)

Interview Questions

1. What is the difference between a stored procedure and a function?

Answer: Key differences:

• Return: Procedures can return multiple result sets; functions return one value or table
• Usage: Functions can be used in SELECT/WHERE; procedures must use EXEC
• Side effects: Functions should be deterministic (no data modification); procedures can modify data
• Transactions: Procedures can manage transactions; functions cannot
• Error handling: Procedures support TRY-CATCH; functions do not

Use procedures for operations that modify data or have side effects. Use functions for calculations and data retrieval that can be composed in queries.

2. What are the INSERTED and DELETED tables in triggers?

Answer: These are special virtual tables available inside DML triggers:

• INSERTED: Contains new rows (for INSERT) or new values (for UPDATE)
• DELETED: Contains removed rows (for DELETE) or old values (for UPDATE)

For INSERT: Only INSERTED has data For DELETE: Only DELETED has data For UPDATE: Both have data (DELETED=old values, INSERTED=new values)

These tables have the same schema as the base table and can contain multiple rows.

3. What is the difference between AFTER and INSTEAD OF triggers?

Answer:

• AFTER: Fires after the DML operation completes. The data change has already happened. Can be used for auditing, cascading changes, or validation that rolls back.
• INSTEAD OF: Replaces the DML operation entirely. The original INSERT/UPDATE/DELETE does not happen unless you explicitly do it in the trigger. Used for updatable views or implementing soft deletes.

INSTEAD OF triggers fire before constraints are checked; AFTER triggers fire after.

4. Why should you avoid using scalar functions in WHERE clauses?

Answer: Scalar functions in WHERE clauses cause performance problems because:

• The function is called once per row (row-by-row execution)
• The optimizer cannot push predicates into the function
• Indexes cannot be used effectively
• Parallel execution may be disabled

Better alternatives:

• Use inline table-valued functions
• Rewrite logic as JOIN conditions
• Use computed columns with indexes
• Inline the calculation in the query

5. How do you prevent SQL injection in stored procedures?

Answer:

1. Use parameters - Never concatenate user input into SQL strings
2. Use sp_executesql for dynamic SQL with proper parameterization
3. Validate input - Check data types, lengths, and ranges
4. Use QUOTENAME for object names in dynamic SQL
5. Limit permissions - Execute-only access, no direct table access

-- Safe dynamic SQL
EXEC sp_executesql
    N'SELECT * FROM @TableName WHERE Id = @Id',
    N'@TableName SYSNAME, @Id INT',
    @TableName = QUOTENAME(@UserInput),
    @Id = @UserId;

Sources

• Microsoft Docs: Stored Procedures
• Microsoft Docs: User-Defined Functions
• Microsoft Docs: DML Triggers
• SQL Server Trigger Best Practices