SQL Stored Procedures

SQL Tutorial

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

✅ What Are SQL 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


 

Call it:

CALL GetCustomerOrders(5);

🟪 SQL Server — CREATE PROCEDURE


Execute:

EXEC GetOrdersByStatus 'shipped';

🟧 PostgreSQL — CREATE PROCEDURE

PostgreSQL supports both functions and procedures.
Procedures use CALL.


Call it:

CALL log_sale(125.50);

🔥 Stored Procedure With Output Parameter (SQL Server)


Use it:

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

🔥 Stored Procedure With Multiple Queries (MySQL)


 

Returns two result sets.

🔥 Stored Procedure With Loops & Logic (PostgreSQL)


📌 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

You may also like...