Function vs Stored Procedure in SQL

Posted by Kyle Hankinson January 10, 2024

---

Functions and stored procedures are both reusable blocks of SQL logic, but they have important differences that affect when you should use each one.

Key Differences

	Function	Stored Procedure
Returns	A single value or table	Optional output parameters
Use in SELECT	Yes	No
Transaction control	No (usually)	Yes (COMMIT, ROLLBACK)
Side effects	Not allowed (in most DBs)	Allowed (INSERT, UPDATE, DELETE)
Error handling	Limited	Full TRY/CATCH

SQL Server

Scalar Function

CREATE FUNCTION dbo.GetFullName(@first VARCHAR(50), @last VARCHAR(50))
RETURNS VARCHAR(101)
AS
BEGIN
    RETURN @first + ' ' + @last;
END;

-- Use in a query
SELECT dbo.GetFullName(first_name, last_name) AS full_name FROM employees;

Table-Valued Function

CREATE FUNCTION dbo.GetOrdersByCustomer(@customer_id INT)
RETURNS TABLE
AS
RETURN (
    SELECT order_id, order_date, total
    FROM orders
    WHERE customer_id = @customer_id
);

-- Use like a table
SELECT * FROM dbo.GetOrdersByCustomer(42);

Stored Procedure

CREATE PROCEDURE dbo.CreateOrder
    @customer_id INT,
    @total DECIMAL(10,2),
    @order_id INT OUTPUT
AS
BEGIN
    INSERT INTO orders (customer_id, total, order_date)
    VALUES (@customer_id, @total, GETDATE());

    SET @order_id = SCOPE_IDENTITY();
END;

-- Call it
DECLARE @new_id INT;
EXEC dbo.CreateOrder @customer_id = 42, @total = 99.99, @order_id = @new_id OUTPUT;

PostgreSQL

PostgreSQL blurred the line between functions and procedures until version 11, which introduced true CREATE PROCEDURE.

Function

CREATE FUNCTION get_full_name(first TEXT, last TEXT)
RETURNS TEXT AS $$
BEGIN
    RETURN first || ' ' || last;
END;
$$ LANGUAGE plpgsql;

SELECT get_full_name('Alice', 'Smith');

Procedure (PostgreSQL 11+)

CREATE PROCEDURE transfer_funds(sender INT, receiver INT, amount DECIMAL)
LANGUAGE plpgsql AS $$
BEGIN
    UPDATE accounts SET balance = balance - amount WHERE id = sender;
    UPDATE accounts SET balance = balance + amount WHERE id = receiver;
    COMMIT;
END;
$$;

CALL transfer_funds(1, 2, 100.00);

The key difference: procedures can use COMMIT and ROLLBACK. Functions cannot.

MySQL

-- Function
DELIMITER //
CREATE FUNCTION get_tax(price DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN price * 0.08;
END //
DELIMITER ;

SELECT name, price, get_tax(price) AS tax FROM products;

-- Stored Procedure
DELIMITER //
CREATE PROCEDURE get_customer_orders(IN cust_id INT)
BEGIN
    SELECT * FROM orders WHERE customer_id = cust_id;
END //
DELIMITER ;

CALL get_customer_orders(42);

Oracle

-- Function
CREATE OR REPLACE FUNCTION get_full_name(p_first VARCHAR2, p_last VARCHAR2)
RETURN VARCHAR2 IS
BEGIN
    RETURN p_first || ' ' || p_last;
END;

SELECT get_full_name(first_name, last_name) FROM employees;

-- Stored Procedure
CREATE OR REPLACE PROCEDURE create_order(
    p_customer_id IN NUMBER,
    p_total IN NUMBER,
    p_order_id OUT NUMBER
) IS
BEGIN
    INSERT INTO orders (customer_id, total, order_date)
    VALUES (p_customer_id, p_total, SYSDATE)
    RETURNING order_id INTO p_order_id;
    COMMIT;
END;

When to Use Each

Use a function when:

• You need a computed value in a SELECT, WHERE, or ORDER BY
• The logic is a pure calculation with no side effects
• You want to reuse a formula across multiple queries

Use a stored procedure when:

• You need to modify data (INSERT, UPDATE, DELETE)
• You need transaction control (COMMIT, ROLLBACK)
• You have complex business logic with error handling
• You want to return multiple result sets

---