Database Management SystemsSQL Advanced Features

SQL Functions

LevelIntermediate

Duration75 mins

TopicSQL Advanced Features

5 / 5

Functions vs Procedures

Two Tools, Different Purposes

Every database developer eventually faces this question: "Should I use a function or a stored procedure?" The answer isn't always obvious because both serve similar purposes—encapsulating reusable logic in the database. Yet they're fundamentally different constructs designed for different use cases, and choosing incorrectly leads to awkward workarounds, performance problems, or unnecessary limitations.

The confusion is understandable. Both functions and procedures:

Are named, stored routines in the database
Accept input parameters
Contain SQL statements and control flow logic
Can be granted execute permissions to specific users

But beneath these similarities lie critical differences in their design philosophy, capability constraints, and invocation patterns that make each suited to specific scenarios. Understanding these differences transforms ambiguous choices into clear decisions.

What You Will Learn

By the end of this page, you will understand the fundamental philosophical differences between functions and procedures, learn the specific capability constraints each faces, develop a decision framework for choosing between them, and recognize refactoring patterns for converting between the two when requirements change.

Philosophical Foundations

The distinction between functions and procedures traces back to fundamental programming paradigm differences:

Functions embody the mathematical concept of a mapping. A function receives inputs and produces an output. It answers the question: "Given these values, what is the result?" The emphasis is on computation and transformation. In an idealized form, functions are pure—they don't change anything outside themselves; they simply compute and return.

Procedures embody the imperative concept of a command sequence. A procedure receives parameters and performs actions. It answers the question: "Given these parameters, do these things." The emphasis is on action and side effects. Procedures modify database state, handle transactions, and orchestrate complex multi-step operations.

This philosophical difference manifests in every aspect of how databases treat these constructs.

Function Philosophy

•Compute values, don't take actions
•Return is mandatory—function IS its return value
•Can appear inside expressions
•Called implicitly during query evaluation
•Ideally stateless and deterministic
•Multiple invocations in single query

Procedure Philosophy

•Perform actions, may compute values too
•Return optional—procedure IS its side effects
•Cannot appear inside expressions
•Called explicitly with EXECUTE/CALL
•Expected to modify state
•Single invocation per EXECUTE statement

The Conceptual Litmus Test

Ask yourself: 'Is this logic computing or transforming a value?' → Function. 'Is this logic performing an action or orchestrating a process?' → Procedure. This simple question correctly categorizes 90% of cases.

Capability Comparison

While functions and procedures share conceptual similarities, databases impose specific capability constraints on each that reflect their intended purposes. These constraints vary by database system but follow consistent patterns.

Function vs Procedure Capabilities (SQL Server Focus)
Capability	Functions	Stored Procedures
Return value	Required (scalar or table)	Optional (integer return code for status)
Output parameters	Not allowed	Supported (OUT, INOUT)
Result sets (SELECT without INTO)	Not allowed in scalar functions	Unlimited result sets
DML statements (INSERT, UPDATE, DELETE)	Not allowed on permanent tables*	Fully supported
DDL statements (CREATE, ALTER, DROP)	Not allowed	Fully supported
Transaction control (BEGIN/COMMIT/ROLLBACK)	Not allowed	Fully supported
Error handling (TRY/CATCH)	Limited or not allowed	Fully supported
Execute dynamic SQL (EXEC, sp_executesql)	Not allowed	Fully supported
Call procedures	Not allowed	Can call other procedures
Be called from SELECT	Yes	No
Use in WHERE/JOIN conditions	Yes	No
Use in computed columns	Scalar: Yes (with constraints)	No
Use in CHECK constraints	Yes (deterministic)	No

*Note: Functions can modify table variables within the function body but cannot modify permanent tables in most databases.

capability_demonstrations.sql
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;

Invocation Patterns

Perhaps the most practical difference between functions and procedures is where and how they can be called. Functions integrate into SQL's expression syntax; procedures are invoked as standalone commands. This distinction determines which construct fits your use case.

invocation_patterns.sql
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

The Expression Test

If you need to use the logic inside a SELECT list, WHERE clause, JOIN condition, or any SQL expression, you MUST use a function. Procedures cannot appear in these contexts. This single test often determines your choice.

Performance Considerations

Performance characteristics differ significantly between functions and procedures, primarily due to how they're executed within the query engine.

Function Performance Characteristics

•Row-by-row execution — Scalar functions typically execute once per row in the result set, creating overhead for large datasets.
•Inline TVF optimization — Inline table-valued functions can be flattened into the query plan, enabling standard optimization techniques.
•Multi-statement TVF barriers — Multi-statement TVFs block optimizer visibility, leading to poor cardinality estimates.
•SQL Server 2019+ inlining — Eligible scalar functions can be inlined, eliminating row-by-row overhead.
•Deterministic caching — Deterministic functions may have results cached within query execution.
•Parallel execution restrictions — Non-deterministic functions may block parallel query execution.

Procedure Performance Characteristics

•Single execution — Procedures execute once per EXEC call, not per row.
•Full optimization — Each statement in a procedure is independently optimized with full query engine capabilities.
•Plan caching — Procedure execution plans are cached and reused, amortizing compilation cost.
•Parameter sniffing — First execution's parameters shape the cached plan, which can be suboptimal for different parameter values.
•Batch operations — Procedures can efficiently perform set-based operations on many rows.
•No expression overhead — No row-by-row invocation cost since procedures can't be used in expressions.

performance_comparison.sql
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

Cross-Platform Differences

Different database systems have varying rules for function and procedure capabilities. Understanding these differences is crucial for portable database development and migration projects.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- SQL Server: Strictest function limitations
 
-- Functions CANNOT:
-- - Modify permanent tables (INSERT/UPDATE/DELETE)
-- - Use dynamic SQL (EXEC, sp_executesql)
-- - Call stored procedures
-- - Use temporary tables
-- - Use TRY/CATCH (before SQL Server 2019)
-- - Return multiple result sets
-- - Have output parameters
 
-- Functions CAN:
-- - Modify table variables (within the function)
-- - Call other functions
-- - Use CASE, IF, WHILE within limits
-- - Be used in computed columns, views, constraints
 
-- Procedures have no such limitations
 
-- Unique SQL Server features:
-- - Scalar UDF inlining (2019+)
-- - SCHEMABINDING for determinism
-- - Inline vs multi-statement TVF distinction

Decision Framework

Given the differences we've explored, how do you decide between a function and a procedure? Here's a systematic decision framework based on your requirements.

decision_framework.txt
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)

Quick Decision Rules

•"I need this in a WHERE clause" → Function
•"I need to update multiple tables" → Procedure (SQL Server) or consider platform
•"I need to control transactions" → Procedure
•"I need to return computed values" → Function
•"I need audit logging with the computation" → Procedure, or split logic
•"I need an expression-embeddable interface" → Function
•"I need maximum flexibility" → Procedure

Hybrid Patterns

Sometimes you need capabilities from both functions and procedures. Rather than choosing, you can combine them using hybrid patterns that leverage the strengths of each.

hybrid_patterns.sql
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

The Separation Principle

When possible, separate pure calculation logic (function) from action logic (procedure). This enables maximum reusability—the function can be used in computed columns, constraints, and ad-hoc queries, while the procedure orchestrates business processes that use those calculations.

Refactoring Between Types

Requirements change, and sometimes you need to convert between functions and procedures. Understanding transformation patterns helps you refactor effectively.

refactoring_patterns.sql
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
-- =====================================================
-- PROCEDURE → FUNCTION: Extract pure computation
-- =====================================================
 
-- Original: Procedure that computes AND acts
CREATE PROCEDURE dbo.GetDiscountedPrice_Old
    @ProductId INT,
    @CustomerId INT,
    @Quantity INT,
    @FinalPrice DECIMAL(18,2) OUTPUT
AS
BEGIN
    DECLARE @BasePrice DECIMAL(18,2);
    DECLARE @DiscountRate DECIMAL(5,4);
    
    SELECT @BasePrice = Price FROM Products WHERE ProductId = @ProductId;
    
    SELECT @DiscountRate = DiscountRate 
    FROM CustomerDiscounts 
    WHERE CustomerId = @CustomerId;
    
    SET @FinalPrice = @BasePrice * @Quantity * (1 - ISNULL(@DiscountRate, 0));
    
    -- Log this calculation
    INSERT INTO PriceCalculationLog VALUES (@ProductId, @CustomerId, @FinalPrice, GETDATE());
END;
 
-- Refactored: Pure function + wrapper procedure
CREATE FUNCTION dbo.CalculateDiscountedPrice(
    @BasePrice DECIMAL(18,2),
    @Quantity INT,
    @DiscountRate DECIMAL(5,4)
)
RETURNS DECIMAL(18,2)
WITH SCHEMABINDING
AS
BEGIN
    RETURN @BasePrice * @Quantity * (1 - ISNULL(@DiscountRate, 0));
END;
 
-- Function can now be used in queries:
SELECT ProductId, dbo.CalculateDiscountedPrice(Price, 10, 0.1) AS BulkPrice
FROM Products;
 
-- =====================================================
-- FUNCTION → PROCEDURE: Add side effects
-- =====================================================
 
-- Original: Function that needs to log (impossible in SQL Server)
CREATE FUNCTION dbo.CalculateShipping_Old(@Weight DECIMAL(10,2), @ZoneId INT)
RETURNS DECIMAL(8,2)
AS
BEGIN
    -- CANNOT log: INSERT INTO ShippingCalcLog VALUES (@Weight, @ZoneId, GETDATE());
    RETURN @Weight * 0.5 + @ZoneId * 2.0;
END;
 
-- Refactored: Keep function, add procedure wrapper
-- Function stays pure for use in constraints, computed columns
CREATE FUNCTION dbo.CalculateShipping(@Weight DECIMAL(10,2), @ZoneId INT)
RETURNS DECIMAL(8,2)
WITH SCHEMABINDING
AS
BEGIN
    RETURN @Weight * 0.5 + @ZoneId * 2.0;
END;
 
-- Procedure adds logging
CREATE PROCEDURE dbo.CalculateShippingWithLog
    @Weight DECIMAL(10,2),
    @ZoneId INT,
    @ShippingCost DECIMAL(8,2) OUTPUT
AS
BEGIN
    SET @ShippingCost = dbo.CalculateShipping(@Weight, @ZoneId);
    
    INSERT INTO ShippingCalcLog (Weight, ZoneId, Result, CalcDate)
    VALUES (@Weight, @ZoneId, @ShippingCost, GETDATE());
END;
 
-- =====================================================
-- MULTI-STATEMENT TVF → INLINE TVF (for performance)
-- =====================================================
 
-- Original: Multi-statement TVF (performance problem)
CREATE FUNCTION dbo.GetActiveOrders_Old(@CustomerId INT)
RETURNS @Orders TABLE (OrderId INT, OrderDate DATE, Total DECIMAL(18,2))
AS
BEGIN
    INSERT INTO @Orders
    SELECT OrderId, OrderDate, TotalAmount
    FROM Orders
    WHERE CustomerId = @CustomerId AND Status = 'ACTIVE';
    
    RETURN;
END;
 
-- Refactored: Inline TVF (optimizer-friendly)
CREATE FUNCTION dbo.GetActiveOrders(@CustomerId INT)
RETURNS TABLE
AS
RETURN
(
    SELECT OrderId, OrderDate, TotalAmount AS Total
    FROM Orders
    WHERE CustomerId = @CustomerId AND Status = 'ACTIVE'
);

Summary: Choosing the Right Tool

Functions and stored procedures serve different purposes in database programming. Understanding their philosophical foundations, capability constraints, and appropriate use cases enables you to choose the right tool for each situation.

Key Takeaways

•Functions compute, procedures act — This philosophical difference drives all capability distinctions.
•Functions integrate into SQL expressions — Use functions when you need results in SELECT, WHERE, JOIN, or constraints.
•Procedures have no capability restrictions — DML, DDL, transactions, dynamic SQL, multiple result sets—procedures can do it all.
•Performance differs significantly — Scalar functions pay per-row overhead; procedures execute once with full optimization.
•Platform rules vary — PostgreSQL and Oracle functions have more capabilities than SQL Server functions.
•Hybrid patterns combine strengths — Separate pure computation (function) from actions (procedure) for maximum flexibility.
•The expression test decides quickly — If you need it in an expression context, it must be a function.

Module Complete

Congratulations! You have completed the Functions module. You now understand user-defined functions comprehensively—from basic scalar functions through table-valued functions, determinism, and the critical distinctions from stored procedures. Apply this knowledge to encapsulate reusable logic effectively in your database applications.

5 / 5

Loading learning content...

Database Management SystemsSQL Advanced Features

SQL Functions

LevelIntermediate

Duration75 mins

TopicSQL Advanced Features

5 / 5

Functions vs Procedures

Two Tools, Different Purposes

The confusion is understandable. Both functions and procedures:

Are named, stored routines in the database
Accept input parameters
Contain SQL statements and control flow logic
Can be granted execute permissions to specific users

What You Will Learn

Philosophical Foundations

The distinction between functions and procedures traces back to fundamental programming paradigm differences:

This philosophical difference manifests in every aspect of how databases treat these constructs.

Function Philosophy

•Compute values, don't take actions
•Return is mandatory—function IS its return value
•Can appear inside expressions
•Called implicitly during query evaluation
•Ideally stateless and deterministic
•Multiple invocations in single query

Procedure Philosophy

•Perform actions, may compute values too
•Return optional—procedure IS its side effects
•Cannot appear inside expressions
•Called explicitly with EXECUTE/CALL
•Expected to modify state
•Single invocation per EXECUTE statement

The Conceptual Litmus Test

Capability Comparison

Function vs Procedure Capabilities (SQL Server Focus)
Capability	Functions	Stored Procedures
Return value	Required (scalar or table)	Optional (integer return code for status)
Output parameters	Not allowed	Supported (OUT, INOUT)
Result sets (SELECT without INTO)	Not allowed in scalar functions	Unlimited result sets
DML statements (INSERT, UPDATE, DELETE)	Not allowed on permanent tables*	Fully supported
DDL statements (CREATE, ALTER, DROP)	Not allowed	Fully supported
Transaction control (BEGIN/COMMIT/ROLLBACK)	Not allowed	Fully supported
Error handling (TRY/CATCH)	Limited or not allowed	Fully supported
Execute dynamic SQL (EXEC, sp_executesql)	Not allowed	Fully supported
Call procedures	Not allowed	Can call other procedures
Be called from SELECT	Yes	No
Use in WHERE/JOIN conditions	Yes	No
Use in computed columns	Scalar: Yes (with constraints)	No
Use in CHECK constraints	Yes (deterministic)	No

*Note: Functions can modify table variables within the function body but cannot modify permanent tables in most databases.

capability_demonstrations.sql
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;

Invocation Patterns

invocation_patterns.sql
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

The Expression Test

Performance Considerations

Performance characteristics differ significantly between functions and procedures, primarily due to how they're executed within the query engine.

Function Performance Characteristics

•Row-by-row execution — Scalar functions typically execute once per row in the result set, creating overhead for large datasets.
•Inline TVF optimization — Inline table-valued functions can be flattened into the query plan, enabling standard optimization techniques.
•Multi-statement TVF barriers — Multi-statement TVFs block optimizer visibility, leading to poor cardinality estimates.
•SQL Server 2019+ inlining — Eligible scalar functions can be inlined, eliminating row-by-row overhead.
•Deterministic caching — Deterministic functions may have results cached within query execution.
•Parallel execution restrictions — Non-deterministic functions may block parallel query execution.

Procedure Performance Characteristics

•Single execution — Procedures execute once per EXEC call, not per row.
•Full optimization — Each statement in a procedure is independently optimized with full query engine capabilities.
•Plan caching — Procedure execution plans are cached and reused, amortizing compilation cost.
•Parameter sniffing — First execution's parameters shape the cached plan, which can be suboptimal for different parameter values.
•Batch operations — Procedures can efficiently perform set-based operations on many rows.
•No expression overhead — No row-by-row invocation cost since procedures can't be used in expressions.

performance_comparison.sql
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

Cross-Platform Differences

Different database systems have varying rules for function and procedure capabilities. Understanding these differences is crucial for portable database development and migration projects.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- SQL Server: Strictest function limitations
 
-- Functions CANNOT:
-- - Modify permanent tables (INSERT/UPDATE/DELETE)
-- - Use dynamic SQL (EXEC, sp_executesql)
-- - Call stored procedures
-- - Use temporary tables
-- - Use TRY/CATCH (before SQL Server 2019)
-- - Return multiple result sets
-- - Have output parameters
 
-- Functions CAN:
-- - Modify table variables (within the function)
-- - Call other functions
-- - Use CASE, IF, WHILE within limits
-- - Be used in computed columns, views, constraints
 
-- Procedures have no such limitations
 
-- Unique SQL Server features:
-- - Scalar UDF inlining (2019+)
-- - SCHEMABINDING for determinism
-- - Inline vs multi-statement TVF distinction

Decision Framework

Given the differences we've explored, how do you decide between a function and a procedure? Here's a systematic decision framework based on your requirements.

decision_framework.txt
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)

Quick Decision Rules

•"I need this in a WHERE clause" → Function
•"I need to update multiple tables" → Procedure (SQL Server) or consider platform
•"I need to control transactions" → Procedure
•"I need to return computed values" → Function
•"I need audit logging with the computation" → Procedure, or split logic
•"I need an expression-embeddable interface" → Function
•"I need maximum flexibility" → Procedure

Hybrid Patterns

Sometimes you need capabilities from both functions and procedures. Rather than choosing, you can combine them using hybrid patterns that leverage the strengths of each.

hybrid_patterns.sql
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

The Separation Principle

Refactoring Between Types

Requirements change, and sometimes you need to convert between functions and procedures. Understanding transformation patterns helps you refactor effectively.

refactoring_patterns.sql
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
-- =====================================================
-- PROCEDURE → FUNCTION: Extract pure computation
-- =====================================================
 
-- Original: Procedure that computes AND acts
CREATE PROCEDURE dbo.GetDiscountedPrice_Old
    @ProductId INT,
    @CustomerId INT,
    @Quantity INT,
    @FinalPrice DECIMAL(18,2) OUTPUT
AS
BEGIN
    DECLARE @BasePrice DECIMAL(18,2);
    DECLARE @DiscountRate DECIMAL(5,4);
    
    SELECT @BasePrice = Price FROM Products WHERE ProductId = @ProductId;
    
    SELECT @DiscountRate = DiscountRate 
    FROM CustomerDiscounts 
    WHERE CustomerId = @CustomerId;
    
    SET @FinalPrice = @BasePrice * @Quantity * (1 - ISNULL(@DiscountRate, 0));
    
    -- Log this calculation
    INSERT INTO PriceCalculationLog VALUES (@ProductId, @CustomerId, @FinalPrice, GETDATE());
END;
 
-- Refactored: Pure function + wrapper procedure
CREATE FUNCTION dbo.CalculateDiscountedPrice(
    @BasePrice DECIMAL(18,2),
    @Quantity INT,
    @DiscountRate DECIMAL(5,4)
)
RETURNS DECIMAL(18,2)
WITH SCHEMABINDING
AS
BEGIN
    RETURN @BasePrice * @Quantity * (1 - ISNULL(@DiscountRate, 0));
END;
 
-- Function can now be used in queries:
SELECT ProductId, dbo.CalculateDiscountedPrice(Price, 10, 0.1) AS BulkPrice
FROM Products;
 
-- =====================================================
-- FUNCTION → PROCEDURE: Add side effects
-- =====================================================
 
-- Original: Function that needs to log (impossible in SQL Server)
CREATE FUNCTION dbo.CalculateShipping_Old(@Weight DECIMAL(10,2), @ZoneId INT)
RETURNS DECIMAL(8,2)
AS
BEGIN
    -- CANNOT log: INSERT INTO ShippingCalcLog VALUES (@Weight, @ZoneId, GETDATE());
    RETURN @Weight * 0.5 + @ZoneId * 2.0;
END;
 
-- Refactored: Keep function, add procedure wrapper
-- Function stays pure for use in constraints, computed columns
CREATE FUNCTION dbo.CalculateShipping(@Weight DECIMAL(10,2), @ZoneId INT)
RETURNS DECIMAL(8,2)
WITH SCHEMABINDING
AS
BEGIN
    RETURN @Weight * 0.5 + @ZoneId * 2.0;
END;
 
-- Procedure adds logging
CREATE PROCEDURE dbo.CalculateShippingWithLog
    @Weight DECIMAL(10,2),
    @ZoneId INT,
    @ShippingCost DECIMAL(8,2) OUTPUT
AS
BEGIN
    SET @ShippingCost = dbo.CalculateShipping(@Weight, @ZoneId);
    
    INSERT INTO ShippingCalcLog (Weight, ZoneId, Result, CalcDate)
    VALUES (@Weight, @ZoneId, @ShippingCost, GETDATE());
END;
 
-- =====================================================
-- MULTI-STATEMENT TVF → INLINE TVF (for performance)
-- =====================================================
 
-- Original: Multi-statement TVF (performance problem)
CREATE FUNCTION dbo.GetActiveOrders_Old(@CustomerId INT)
RETURNS @Orders TABLE (OrderId INT, OrderDate DATE, Total DECIMAL(18,2))
AS
BEGIN
    INSERT INTO @Orders
    SELECT OrderId, OrderDate, TotalAmount
    FROM Orders
    WHERE CustomerId = @CustomerId AND Status = 'ACTIVE';
    
    RETURN;
END;
 
-- Refactored: Inline TVF (optimizer-friendly)
CREATE FUNCTION dbo.GetActiveOrders(@CustomerId INT)
RETURNS TABLE
AS
RETURN
(
    SELECT OrderId, OrderDate, TotalAmount AS Total
    FROM Orders
    WHERE CustomerId = @CustomerId AND Status = 'ACTIVE'
);

Summary: Choosing the Right Tool

Key Takeaways

•Functions compute, procedures act — This philosophical difference drives all capability distinctions.
•Functions integrate into SQL expressions — Use functions when you need results in SELECT, WHERE, JOIN, or constraints.
•Procedures have no capability restrictions — DML, DDL, transactions, dynamic SQL, multiple result sets—procedures can do it all.
•Performance differs significantly — Scalar functions pay per-row overhead; procedures execute once with full optimization.
•Platform rules vary — PostgreSQL and Oracle functions have more capabilities than SQL Server functions.
•Hybrid patterns combine strengths — Separate pure computation (function) from actions (procedure) for maximum flexibility.
•The expression test decides quickly — If you need it in an expression context, it must be a function.

Module Complete

5 / 5