CodeCapsule

SQL Stored Procedures

by

Here is a clear, complete, and practical guide to SQL Stored Procedures, with examples for MySQL, PostgreSQL, and SQL Server.

✅ What Are Stored Procedures?

A stored procedure is a precompiled block of SQL code stored on the database server.

They allow you to:

  • Encapsulate business logic

  • Reduce repeated SQL code

  • Improve performance (precompiled execution)

  • Enhance security (limit direct table access)

  • Accept parameters and return results

🔹 Basic Structure of a Stored Procedure

A stored procedure typically includes:

  • A name

  • Input / output parameters

  • One or more SQL statements

  • Optional control flow (IF, CASE, WHILE)

🟦 MySQL — CREATE PROCEDURE

DELIMITER $$

CREATE PROCEDURE GetCustomerOrders(IN customerId INT)
BEGIN
SELECT *
FROM orders
WHERE customer_id = customerId;
END $$

DELIMITER ;

Call it:

CALL GetCustomerOrders(5);

🟪 SQL Server — CREATE PROCEDURE

CREATE PROCEDURE GetOrdersByStatus
@status VARCHAR(20)
AS
BEGIN
SELECT *
FROM orders
WHERE status = @status;
END;

Execute:

EXEC GetOrdersByStatus 'shipped';

🟧 PostgreSQL — CREATE PROCEDURE

PostgreSQL supports both functions and procedures.
Procedures use CALL.

CREATE PROCEDURE log_sale(IN amount numeric)
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO sales_log(amount, created_at)
VALUES (amount, NOW());
END;
$$;

Call it:

CALL log_sale(125.50);

🔥 Stored Procedure With Output Parameter (SQL Server)

CREATE PROCEDURE CountOrders
@orderCount INT OUTPUT
AS
BEGIN
SELECT @orderCount = COUNT(*) FROM orders;
END;

Use it:

DECLARE @c INT;
EXEC CountOrders @orderCount = @c OUTPUT;
SELECT @c AS TotalOrders;

🔥 Stored Procedure With Multiple Queries (MySQL)

DELIMITER $$

CREATE PROCEDURE GetUserSummary(IN userId INT)
BEGIN
SELECT * FROM users WHERE id = userId;
SELECT * FROM orders WHERE user_id = userId;
END $$

DELIMITER ;

Returns two result sets.

🔥 Stored Procedure With Loops & Logic (PostgreSQL)

CREATE PROCEDURE process_orders()
LANGUAGE plpgsql
AS $$
DECLARE
r RECORD;
BEGIN
FOR r IN SELECT id FROM orders WHERE status = 'pending'
LOOP
UPDATE orders
SET status = 'processing'
WHERE id = r.id;
END LOOP;
END;
$$;

📌 Modify or Drop Stored Procedures

MySQL / SQL Server:

DROP PROCEDURE IF EXISTS ProcedureName;

PostgreSQL:

DROP PROCEDURE procedure_name(args);

🧠 Best Practices

✔ Use parameters to avoid SQL injection
✔ Keep stored procedures small and modular
✔ Include error handling (TRY/CATCH in SQL Server, EXCEPTION in PostgreSQL)
✔ Avoid dynamic SQL unless necessary
✔ Use version control for stored procedure code
✔ Return values in a consistent format

🧩 When to Use Stored Procedures

✔ Business rules executed frequently
✔ Complex multi-step operations
✔ Audit logging
✔ Batch processing
✔ Reducing network traffic (server-side execution)

Avoid for:

  • Simple SELECT queries

  • Logic better served in the application layer

CodeCapsule

Sanjit Sinha — Web Developer | PHP • Laravel • CodeIgniter • MySQL • Bootstrap Founder, CodeCapsule — Student projects & practical coding guides. Email: info@codecapsule.in • Website: CodeCapsule.in

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *