SQL Notes
Learn how to create Stored Procedures in SQL, understand syntax, examples, parameters, best practices, and real-world usage.
Writing the same SQL query over and over in different parts of your application is tedious, error-prone, and hard to maintain. Stored procedures solve this by letting you save a collection of SQL statements in the database and call them by name whenever needed — like saving a recipe and cooking it any time instead of figuring out ingredients from scratch each time.
A stored procedure is a precompiled collection of SQL statements stored in the database. Once created, it can be executed repeatedly without rewriting the SQL. This makes your database code reusable, secure, and significantly faster since the execution plan is cached.
In a typical enterprise application, stored procedures form the data access layer. Instead of writing raw SQL queries scattered throughout your application code, you centralize all database logic inside procedures. When business rules change — say the discount calculation formula updates — you modify one procedure instead of hunting through dozens of code files. This architectural pattern has been the standard in professional database development for decades, and understanding how to create procedures is a fundamental skill.
Basic Syntax
SQL Server
CREATE PROCEDURE ProcedureName
AS
BEGIN
-- SQL statements here
END;MySQL
DELIMITER //
CREATE PROCEDURE ProcedureName()
BEGIN
-- SQL statements here
END //
DELIMITER ;The DELIMITER command in MySQL is necessary because the procedure body contains semicolons. Without changing the delimiter, MySQL would think the procedure ends at the first semicolon inside the body.
Your First Stored Procedure
Let us create a simple procedure that retrieves all employees:
SQL Server
CREATE PROCEDURE GetAllEmployees
AS
BEGIN
SELECT EmpID, Name, Department, Salary, JoinDate
FROM Employees
ORDER BY Name;
END;MySQL
DELIMITER //
CREATE PROCEDURE GetAllEmployees()
BEGIN
SELECT EmpID, Name, Department, Salary, JoinDate
FROM Employees
ORDER BY Name;
END //
DELIMITER ;Execute it:
-- SQL Server
EXEC GetAllEmployees;
-- MySQL
CALL GetAllEmployees();Procedures with Parameters
Real procedures almost always accept parameters to make them dynamic:
Parameters transform a static procedure into a flexible tool. Without parameters, a procedure does the same thing every time — like a vending machine with one button. With parameters, the same procedure can handle countless different scenarios. You pass in the data, and the procedure uses it in its queries. This is how real applications work: the user interface collects input from the user, passes it as parameters to a stored procedure, and displays whatever the procedure returns.
SQL Server
CREATE PROCEDURE GetEmployeesByDepartment
@DeptName VARCHAR(50)
AS
BEGIN
SELECT EmpID, Name, Salary, JoinDate
FROM Employees
WHERE Department = @DeptName
ORDER BY Salary DESC;
END;EXEC GetEmployeesByDepartment @DeptName = 'Engineering';
EXEC GetEmployeesByDepartment @DeptName = 'Marketing';MySQL
DELIMITER //
CREATE PROCEDURE GetEmployeesByDepartment(IN dept_name VARCHAR(50))
BEGIN
SELECT EmpID, Name, Salary, JoinDate
FROM Employees
WHERE Department = dept_name
ORDER BY Salary DESC;
END //
DELIMITER ;CALL GetEmployeesByDepartment('Engineering');Procedures with Multiple Parameters
CREATE PROCEDURE GetFilteredEmployees
@Department VARCHAR(50),
@MinSalary DECIMAL(10,2),
@JoinAfter DATE
AS
BEGIN
SELECT EmpID, Name, Department, Salary, JoinDate
FROM Employees
WHERE Department = @Department
AND Salary >= @MinSalary
AND JoinDate >= @JoinAfter
ORDER BY JoinDate DESC;
END;EXEC GetFilteredEmployees
@Department = 'Engineering',
@MinSalary = 50000,
@JoinAfter = '2025-01-01';Procedures with Logic
Stored procedures can contain variables, conditions, and loops:
The real power of stored procedures emerges when you combine SQL queries with procedural logic. Variables let you store intermediate results. IF-ELSE conditions let you make decisions based on data. Loops let you process multiple records. Error handling lets you recover gracefully from failures. Together, these features let you encode complex business workflows entirely within the database, ensuring consistent behavior regardless of which application or user triggers the operation.
CREATE PROCEDURE ProcessOrder
@OrderID INT
AS
BEGIN
DECLARE @OrderTotal DECIMAL(10,2);
DECLARE @CustomerID INT;
-- Calculate order total
SELECT @OrderTotal = SUM(Quantity * UnitPrice)
FROM OrderItems
WHERE OrderID = @OrderID;
-- Get customer
SELECT @CustomerID = CustomerID
FROM Orders WHERE OrderID = @OrderID;
-- Update order total
UPDATE Orders
SET TotalAmount = @OrderTotal
WHERE OrderID = @OrderID;
-- Apply loyalty points
IF @OrderTotal > 5000
BEGIN
UPDATE Customers
SET LoyaltyPoints = LoyaltyPoints + 50
WHERE CustomerID = @CustomerID;
END
-- Update order status
UPDATE Orders SET Status = 'Processed' WHERE OrderID = @OrderID;
END;Real-World Example: Student Registration
DELIMITER //
CREATE PROCEDURE RegisterStudent(
IN p_name VARCHAR(100),
IN p_email VARCHAR(255),
IN p_course VARCHAR(50),
IN p_semester INT
)
BEGIN
-- Check if email already exists
IF EXISTS (SELECT 1 FROM Students WHERE Email = p_email) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Email already registered';
END IF;
-- Insert the student
INSERT INTO Students (Name, Email, Course, Semester, EnrollDate, Status)
VALUES (p_name, p_email, p_course, p_semester, CURRENT_DATE, 'Active');
-- Create library card
INSERT INTO LibraryCards (StudentID, IssueDate, ExpiryDate)
VALUES (LAST_INSERT_ID(), CURRENT_DATE, DATE_ADD(CURRENT_DATE, INTERVAL 4 YEAR));
-- Return the new student record
SELECT * FROM Students WHERE StudentID = LAST_INSERT_ID();
END //
DELIMITER ;CALL RegisterStudent('Rahul Sharma', 'rahul@univ.edu', 'BCA', 1);Real-World Example: Inventory Management
CREATE PROCEDURE RestockProduct
@ProductID INT,
@Quantity INT,
@SupplierID INT
AS
BEGIN
-- Update stock level
UPDATE Products
SET Stock = Stock + @Quantity,
LastRestockDate = GETDATE()
WHERE ProductID = @ProductID;
-- Log the restock event
INSERT INTO RestockLog (ProductID, Quantity, SupplierID, RestockDate)
VALUES (@ProductID, @Quantity, @SupplierID, GETDATE());
-- Check if product was previously out of stock
IF EXISTS (
SELECT 1 FROM Products
WHERE ProductID = @ProductID AND Status = 'Out of Stock'
)
BEGIN
UPDATE Products SET Status = 'Available' WHERE ProductID = @ProductID;
END
-- Return updated product info
SELECT ProductID, ProductName, Stock, Status
FROM Products WHERE ProductID = @ProductID;
END;Modifying an Existing Procedure
If you need to change a procedure's logic:
SQL Server
ALTER PROCEDURE GetAllEmployees
AS
BEGIN
SELECT EmpID, Name, Department, Salary, JoinDate, Email
FROM Employees
WHERE Status = 'Active'
ORDER BY Name;
END;MySQL
MySQL does not support ALTER PROCEDURE for the body. You must drop and recreate:
DROP PROCEDURE IF EXISTS GetAllEmployees;
DELIMITER //
CREATE PROCEDURE GetAllEmployees()
BEGIN
SELECT EmpID, Name, Department, Salary, JoinDate, Email
FROM Employees
WHERE Status = 'Active'
ORDER BY Name;
END //
DELIMITER ;Dropping a Procedure
-- Safe drop (no error if it does not exist)
DROP PROCEDURE IF EXISTS GetAllEmployees;
-- Direct drop (error if it does not exist)
DROP PROCEDURE GetAllEmployees;Viewing Existing Procedures
SQL Server
-- List all procedures
SELECT name, create_date, modify_date
FROM sys.procedures
ORDER BY modify_date DESC;
-- View procedure definition
EXEC sp_helptext 'GetAllEmployees';MySQL
-- List all procedures
SHOW PROCEDURE STATUS WHERE Db = 'your_database';
-- View procedure definition
SHOW CREATE PROCEDURE GetAllEmployees;Common Mistakes to Avoid
Mistake 1: Forgetting DELIMITER in MySQL. Without changing the delimiter, MySQL ends the procedure at the first semicolon in the body.
Mistake 2: Not using BEGIN...END. Always wrap procedure bodies in BEGIN...END, even for single statements. It makes the code clearer.
Mistake 3: Not handling errors. Procedures can fail mid-execution. Without error handling, partial changes may corrupt data.
Mistake 4: Creating procedures that are too large. If a procedure exceeds 100 lines, consider breaking it into smaller procedures that call each other.
Best Practices
- Use meaningful names — GetEmployeesByDept is better than proc1
- Always include error handling with TRY-CATCH or handlers
- Comment your procedures explaining the business logic
- Keep procedures focused — one procedure, one responsibility
- Version control your procedures alongside application code
- Test with edge cases including NULL parameters and empty result sets
Stored procedures are the workhorses of professional database development. They encapsulate business logic, improve security, and make your applications faster and more maintainable. Master creating them, and you have mastered one of the most important skills in SQL development.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Create Stored Procedure in SQL.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL Complete Guide topic.
Search Terms
sql-complete-guide, sql complete guide, sql, complete, guide, stored, procedures, create
Related SQL Complete Guide Topics