Menu

Earn Premium with Referrals

Invite your friends and earn Premium rewards through our referral program.

See how it works and start inviting friends.

Stored Procedures & Functions
SQL

Stored Procedures & Functions

Master server-side logic to improve performance, enforce security, and reuse complex SQL logic.

Stored Procedures and Functions allow you to store a series of SQL statements on the database server. They can be called by name from your application.

Key Differences

FeatureFunctionStored Procedure
Return ValueMust return a valueMay or may not return values
UsageCan be used in SELECT/WHERECalled using EXEC or CALL
DML SupportUsually read-onlyCan INSERT, UPDATE, DELETE
TransactionsCannot use transactionsCan use COMMIT/ROLLBACK
ParametersIN onlyIN, OUT, INOUT

Stored Procedure Example

-- PostgreSQL/MySQL
CREATE PROCEDURE PlaceOrder(p_user_id INT, p_amount DECIMAL)
LANGUAGE SQL
AS $$
    INSERT INTO orders(user_id, amount, created_at)
    VALUES (p_user_id, p_amount, NOW());
$$;

-- Call it
CALL PlaceOrder(1, 99.99);

Function Example

CREATE FUNCTION GetSalesTax(price DECIMAL) RETURNS DECIMAL(10,2)
DETERMINISTIC
LANGUAGE SQL
AS $$
    RETURN price * 0.08;
$$;

-- Use in a query
SELECT id, amount, GetSalesTax(amount) AS tax FROM orders;

Scalar vs Table-Valued Functions

  • Scalar Function: Returns a single value. Can be used in SELECT, WHERE, etc.
  • Table-Valued Function (TVF): Returns a result set. Can be joined against like a table.
-- Table-valued function (PostgreSQL)
CREATE FUNCTION GetOrdersByUser(p_user_id INT)
RETURNS TABLE(order_id INT, amount DECIMAL, order_date DATE)
LANGUAGE SQL
AS $$
    SELECT order_id, amount, order_date
    FROM orders
    WHERE user_id = p_user_id;
$$;

-- Use it
SELECT * FROM GetOrdersByUser(42);

Dynamic SQL

Dynamic SQL builds and executes a SQL string at runtime. It is powerful but dangerous.

-- SQL Server safe example with parameters
DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM Employees WHERE Name = @name';
EXEC sp_executesql @sql, N'@name NVARCHAR(50)', @name = 'John Doe';

Risks:

  • SQL Injection: If user input is concatenated instead of parameterised, attackers can inject malicious SQL.
  • Performance: Dynamic SQL may produce different plans each time, preventing plan caching.

Stored Procedure vs Application Code

FactorStored ProcedureApplication Code
Network trafficLess (one call for many operations)More (multiple round-trips)
Version controlHarder (not in app repo)Easy (in git)
DebuggingHarder (DB-specific tools)Easy (IDE, logging)
TestingHarder (need DB connection)Easy (mocks, unit tests)
PortabilityDatabase-specific syntaxFramework-agnostic

Rule of thumb: Use stored procedures for database-intensive operations that must be atomic. Keep business logic in application code.

Q: Scalar vs Table Function?

A:

  • Scalar Function: Returns a single value (e.g., GetTax(amount)).
  • Table-Valued Function (TVF): Returns a result set (a “virtual table”) that you can join against.

Q: What is Dynamic SQL and its risks?

A: Dynamic SQL is generating a SQL query string at runtime and executing it (e.g., EXEC('SELECT * FROM ' + @tableName)). The main risk is SQL Injection, where a malicious user provides input that changes the query’s behavior.

Q: How to prevent SQL Injection in Procedures?

A: Use Parameters. Never concatenate strings to build a query. The database treats parameters as data, not as executable code.

Q: Can a stored procedure return multiple result sets?

A: Yes. Many databases support multiple result sets from a single procedure call. This is useful for returning related data (e.g., customer + orders) without multiple round-trips.

1. Create procedure to insert order (MySQL/PostgreSQL context).

CREATE PROCEDURE PlaceOrder(p_user_id INT, p_amount DECIMAL)
BEGIN
    INSERT INTO orders(user_id, amount, created_at)
    VALUES (p_user_id, p_amount, NOW());
END;

2. Create function to calculate tax.

CREATE FUNCTION GetSalesTax(price DECIMAL) RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN price * 0.08;
END;

3. Implement UPSERT (Insert or Update if exists).

-- PostgreSQL syntax
INSERT INTO user_stats (user_id, login_count)
VALUES (1, 1)
ON CONFLICT (user_id)
DO UPDATE SET login_count = user_stats.login_count + 1;

4. Dynamic SQL (Safe approach using parameters).

-- Example for SQL Server
DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM Employees WHERE Name = @name';
EXEC sp_executesql @sql, N'@name NVARCHAR(50)', @name = 'John Doe';

5. Create a procedure with OUT parameter.

CREATE PROCEDURE GetEmployeeCount(OUT total INT)
BEGIN
    SELECT COUNT(*) INTO total FROM employees;
END;

-- Call it
CALL GetEmployeeCount(@count);
SELECT @count;

6. Drop a procedure.

DROP PROCEDURE IF EXISTS PlaceOrder;

My Private Notes

Notes are auto-saved locally to this device.