Functions vs Procedures

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96

-- =====================================================
-- FUNCTION LIMITATIONS: What you CANNOT do
-- =====================================================
 
-- ERROR: Cannot modify permanent tables
CREATE FUNCTION dbo.LogAndCompute(@Value INT)
RETURNS INT
AS
BEGIN
    -- This is NOT ALLOWED:
    -- INSERT INTO AuditLog (Value, LogTime) VALUES (@Value, GETDATE());
    
    RETURN @Value * 2;
END;
 
-- ERROR: Cannot use output parameters
-- CREATE FUNCTION dbo.BadFunction(@Input INT, @Output INT OUTPUT)  -- Invalid!
 
-- ERROR: Cannot return multiple result sets
CREATE FUNCTION dbo.BadFunction(@Id INT)
RETURNS INT
AS
BEGIN
    -- This is NOT ALLOWED:
    -- SELECT * FROM Table1 WHERE Id = @Id;  
    -- SELECT * FROM Table2 WHERE ParentId = @Id;
    
    RETURN @Id;
END;
 
-- ERROR: Cannot use dynamic SQL
CREATE FUNCTION dbo.BadFunction(@TableName VARCHAR(100))
RETURNS INT
AS
BEGIN
    -- This is NOT ALLOWED:
    -- EXEC('SELECT COUNT(*) FROM ' + @TableName);
    
    RETURN 0;
END;
 
-- ERROR: Cannot call stored procedures
CREATE FUNCTION dbo.BadFunction(@Id INT)
RETURNS INT
AS
BEGIN
    -- This is NOT ALLOWED:
    -- EXEC dbo.SomeStoredProcedure @Id;
    
    RETURN @Id * 2;
END;
 
-- =====================================================
-- PROCEDURE CAPABILITIES: Full flexibility
-- =====================================================
 
CREATE PROCEDURE dbo.ProcessOrder
    @OrderId INT,
    @ProcessedCount INT OUTPUT,  -- Output parameter
    @Status VARCHAR(50) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        BEGIN TRANSACTION;  -- Transaction control
        
        -- DML operations
        UPDATE Orders SET Status = 'PROCESSING' WHERE OrderId = @OrderId;
        
        INSERT INTO OrderHistory (OrderId, Action, ActionTime)
        VALUES (@OrderId, 'Started Processing', GETDATE());
        
        -- Dynamic SQL
        DECLARE @SQL NVARCHAR(MAX);
        SET @SQL = N'SELECT @Count = COUNT(*) FROM OrderItems WHERE OrderId = @OId';
        EXEC sp_executesql @SQL, N'@OId INT, @Count INT OUTPUT', @OrderId, @ProcessedCount OUTPUT;
        
        -- Call another procedure
        EXEC dbo.SendNotification @OrderId;
        
        COMMIT TRANSACTION;
        SET @Status = 'SUCCESS';
        
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        SET @Status = 'ERROR: ' + ERROR_MESSAGE();
    END CATCH;
    
    -- Return multiple result sets
    SELECT * FROM Orders WHERE OrderId = @OrderId;
    SELECT * FROM OrderItems WHERE OrderId = @OrderId;
    
    RETURN 0;  -- Return code
END;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70

-- =====================================================
-- FUNCTION INVOCATION: Inside SQL expressions
-- =====================================================
 
-- In SELECT list
SELECT 
    ProductId,
    ProductName,
    Price,
    dbo.CalculateTax(Price, 0.08) AS Tax
FROM Products;
 
-- In WHERE clause
SELECT * FROM Employees
WHERE dbo.GetAge(BirthDate, GETDATE()) >= 21;
 
-- In JOIN conditions
SELECT c.CustomerName, o.*
FROM Customers c
INNER JOIN Orders o ON dbo.NormalizeKey(c.CustomerId) = dbo.NormalizeKey(o.CustomerId);
 
-- As computed column definition
ALTER TABLE Products ADD TaxAmount AS dbo.CalculateTax(Price, 0.08);
 
-- In CHECK constraint
ALTER TABLE Employees ADD CONSTRAINT chk_age CHECK (dbo.GetAge(BirthDate, GETDATE()) >= 16);
 
-- In CASE expression
SELECT 
    CASE dbo.GetCustomerTier(CustomerId)
        WHEN 'GOLD' THEN Price * 0.9
        WHEN 'SILVER' THEN Price * 0.95
        ELSE Price
    END AS FinalPrice
FROM Orders;
 
-- In ORDER BY
SELECT * FROM Products ORDER BY dbo.CalculatePopularityScore(ProductId) DESC;
 
-- Table-valued function in FROM clause
SELECT * FROM dbo.GetOrdersByCustomer(12345) WHERE Total > 100;
 
-- =====================================================
-- PROCEDURE INVOCATION: Standalone commands only
-- =====================================================
 
-- Basic execution
EXEC dbo.ProcessOrder @OrderId = 123;
 
-- Or with full syntax
EXECUTE dbo.ProcessOrder @OrderId = 123;
 
-- With output parameters
DECLARE @ItemCount INT, @Status VARCHAR(50);
EXEC dbo.ProcessOrder @OrderId = 123, @ProcessedCount = @ItemCount OUTPUT, @Status = @Status OUTPUT;
PRINT @Status;
 
-- Capturing return code
DECLARE @ReturnCode INT;
EXEC @ReturnCode = dbo.ProcessOrder @OrderId = 123;
IF @ReturnCode <> 0 PRINT 'Procedure failed';
 
-- CANNOT do these with procedures:
-- SELECT *, dbo.ProcessOrder(OrderId) FROM Orders;  -- Invalid!
-- SELECT * FROM dbo.ProcessOrder(123);  -- Invalid for most procedures!
-- WHERE dbo.ProcessOrder(OrderId) = 0;  -- Invalid!
 
-- PostgreSQL: CALL for procedures, SELECT for functions
-- CALL process_order(123);                -- Procedure
-- SELECT * FROM get_orders_by_customer(123);  -- Function

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

-- Performance scenario: Processing 1 million rows
 
-- FUNCTION APPROACH (potential performance problem)
CREATE FUNCTION dbo.CalculateComplexMetric(@Value1 INT, @Value2 INT, @Value3 INT)
RETURNS DECIMAL(18,6)
AS
BEGIN
    -- Complex calculation
    DECLARE @Result DECIMAL(18,6);
    SET @Result = POWER(CAST(@Value1 AS FLOAT), 2) / 
                  SQRT(CAST(@Value2 AS FLOAT)) * 
                  LOG(CAST(@Value3 AS FLOAT) + 1);
    RETURN @Result;
END;
 
-- This query invokes the function 1 MILLION times
SELECT Id, dbo.CalculateComplexMetric(Col1, Col2, Col3) AS Metric
FROM LargeTable;  -- 1 million rows
-- Elapsed time: 45 seconds (function call overhead dominates)
 
-- INLINE EXPRESSION APPROACH (much faster)
SELECT 
    Id, 
    POWER(CAST(Col1 AS FLOAT), 2) / 
        SQRT(CAST(Col2 AS FLOAT)) * 
        LOG(CAST(Col3 AS FLOAT) + 1) AS Metric
FROM LargeTable;
-- Elapsed time: 2 seconds (optimizer handles efficiently)
 
-- PROCEDURE APPROACH (for batch update)
CREATE PROCEDURE dbo.UpdateAllMetrics
AS
BEGIN
    UPDATE LargeTable
    SET Metric = POWER(CAST(Col1 AS FLOAT), 2) / 
                 SQRT(CAST(Col2 AS FLOAT)) * 
                 LOG(CAST(Col3 AS FLOAT) + 1);
END;
 
EXEC dbo.UpdateAllMetrics;
-- Elapsed time: 3 seconds (single set-based operation)
 
-- When function is REQUIRED (e.g., computed column), consider:
-- 1. Ensure schema binding for potential inlining
-- 2. Use persisted computed column to compute once
-- 3. Index the computed column if frequently filtered

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

FUNCTION VS PROCEDURE DECISION TREE
=====================================
 
START: What does your logic need to do?
 
├─► Need to use in SELECT/WHERE/JOIN expressions?
│   └─► YES → Must use FUNCTION
│
├─► Need to return a value used in SQL expressions?
│   └─► YES → Must use FUNCTION
│
├─► Need to modify permanent tables (INSERT/UPDATE/DELETE)?
│   ├─► SQL Server → Must use PROCEDURE
│   └─► PostgreSQL/Oracle/MySQL → PROCEDURE preferred, some functions allowed
│
├─► Need transaction control (COMMIT/ROLLBACK)?
│   └─► YES → Must use PROCEDURE
│
├─► Need to return multiple result sets?
│   └─► YES → Must use PROCEDURE
│
├─► Need output parameters?
│   └─► YES → PROCEDURE (or table-valued function returning single row)
│
├─► Need dynamic SQL?
│   └─► YES → PROCEDURE (SQL Server) or check platform
│
├─► Use in computed column or indexed view?
│   └─► YES → Must use FUNCTION (deterministic)
│
├─► Check constraint referencing the logic?
│   └─► YES → Must use FUNCTION (deterministic)
│
└─► None of the above?
    └─► Consider PURPOSE:
        ├─► Pure computation → FUNCTION (enables more usage contexts)
        └─► Action/orchestration → PROCEDURE (more capabilities)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107

-- =====================================================
-- PATTERN 1: Function for calculation, Procedure for action
-- Separate concerns into appropriate constructs
-- =====================================================
 
-- Pure calculation function (deterministic, usable anywhere)
CREATE FUNCTION dbo.CalculateOrderTotal(@OrderId INT)
RETURNS DECIMAL(18,2)
AS
BEGIN
    DECLARE @Total DECIMAL(18,2);
    
    SELECT @Total = SUM(Quantity * UnitPrice * (1 - Discount))
    FROM OrderItems
    WHERE OrderId = @OrderId;
    
    RETURN ISNULL(@Total, 0);
END;
 
-- Action procedure that uses the function
CREATE PROCEDURE dbo.FinalizeOrder(@OrderId INT)
AS
BEGIN
    DECLARE @Total DECIMAL(18,2) = dbo.CalculateOrderTotal(@OrderId);
    
    -- Use function result in procedure actions
    UPDATE Orders 
    SET TotalAmount = @Total, 
        Status = 'FINALIZED', 
        FinalizedDate = GETDATE()
    WHERE OrderId = @OrderId;
    
    INSERT INTO OrderAudit (OrderId, Action, Amount, ActionDate)
    VALUES (@OrderId, 'FINALIZED', @Total, GETDATE());
    
    -- Send notification (procedure call)
    EXEC dbo.SendOrderNotification @OrderId, @Total;
END;
 
-- Benefit: CalculateOrderTotal can be used in SELECTs, WHEREs, etc.
-- FinalizeOrder handles all the action/side-effect logic
 
-- =====================================================
-- PATTERN 2: TVF for complex queries, Procedure for orchestration
-- =====================================================
 
-- Reusable query as inline TVF
CREATE FUNCTION dbo.GetPendingOrdersForCustomer(@CustomerId INT)
RETURNS TABLE
AS
RETURN
(
    SELECT o.OrderId, o.OrderDate, o.TotalAmount, o.Status
    FROM Orders o
    WHERE o.CustomerId = @CustomerId
      AND o.Status = 'PENDING'
);
 
-- Procedure uses TVF for reporting and actions
CREATE PROCEDURE dbo.ProcessCustomerOrders(@CustomerId INT)
AS
BEGIN
    -- First, report current state using TVF
    SELECT * FROM dbo.GetPendingOrdersForCustomer(@CustomerId);
    
    -- Then take action
    UPDATE o
    SET Status = 'PROCESSING'
    FROM Orders o
    INNER JOIN dbo.GetPendingOrdersForCustomer(@CustomerId) AS pending ON o.OrderId = pending.OrderId;
    
    -- Log action count
    INSERT INTO ProcessingLog (CustomerId, OrdersProcessed, ProcessedAt)
    VALUES (@CustomerId, @@ROWCOUNT, GETDATE());
END;
 
-- =====================================================
-- PATTERN 3: Wrapper procedure for function with logging
-- When you need function semantics but also want logging
-- =====================================================
 
-- Core logic as function
CREATE FUNCTION dbo.CalculateTaxAmount(@Amount DECIMAL(18,2), @TaxRate DECIMAL(5,4))
RETURNS DECIMAL(18,2)
WITH SCHEMABINDING
AS
BEGIN
    RETURN ROUND(@Amount * @TaxRate, 2);
END;
 
-- Wrapper procedure that logs (for API calls)
CREATE PROCEDURE dbo.CalculateAndLogTax
    @Amount DECIMAL(18,2),
    @TaxRate DECIMAL(5,4),
    @Result DECIMAL(18,2) OUTPUT
AS
BEGIN
    -- Use function for calculation
    SET @Result = dbo.CalculateTaxAmount(@Amount, @TaxRate);
    
    -- Log for auditing
    INSERT INTO TaxCalculationLog (Amount, Rate, Result, CalculatedAt, RequestedBy)
    VALUES (@Amount, @TaxRate, @Result, GETDATE(), SUSER_NAME());
END;
 
-- Application calls procedure for audit trail
-- Database constraints use function directly