CREATE PROCEDURE syntax in SQL - Mini Project: Build & Apply

Create a Simple Stored Procedure in SQL

📖 Scenario: You are working with a small company database. You want to create a stored procedure that helps you quickly find all employees in a specific department.

🎯 Goal: Build a stored procedure named GetEmployeesByDepartment that takes a department name as input and returns all employees who work in that department.

📋 What You'll Learn

Create a stored procedure named GetEmployeesByDepartment

The procedure should accept one input parameter called dept_name of type VARCHAR(50)

The procedure should select all columns from the Employees table where the Department matches dept_name

Use standard SQL syntax for creating procedures

💡 Why This Matters

🌍 Real World

Stored procedures help automate common database tasks like filtering data based on user input, improving efficiency and security.

💼 Career

Knowing how to write stored procedures is important for database administrators and backend developers to create reusable and optimized database operations.

1

Create the Employees table

Create a table called Employees with these columns and types exactly: EmployeeID INT, Name VARCHAR(100), Department VARCHAR(50). Insert these three rows exactly: (1, 'Alice', 'Sales'), (2, 'Bob', 'HR'), (3, 'Charlie', 'Sales').

SQL

# Create the Employees table and insert the rows
# Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Define the procedure header

Write the first line to create a procedure named GetEmployeesByDepartment that takes one input parameter called dept_name of type VARCHAR(50). Use the syntax: CREATE PROCEDURE GetEmployeesByDepartment (IN dept_name VARCHAR(50)).

SQL

CREATE TABLE Employees (
    EmployeeID INT,
    Name VARCHAR(100),
    Department VARCHAR(50)
);

INSERT INTO Employees (EmployeeID, Name, Department) VALUES
(1, 'Alice', 'Sales'),
(2, 'Bob', 'HR'),
(3, 'Charlie', 'Sales');

-- Write the procedure header below
# Your code here

Need a hint?

Start with CREATE PROCEDURE and specify the input parameter with IN.

3

Add the procedure body with SELECT query

Add the procedure body using BEGIN and END. Inside, write a SELECT * FROM Employees WHERE Department = dept_name; statement to return employees matching the input department.

SQL

CREATE TABLE Employees (
    EmployeeID INT,
    Name VARCHAR(100),
    Department VARCHAR(50)
);

INSERT INTO Employees (EmployeeID, Name, Department) VALUES
(1, 'Alice', 'Sales'),
(2, 'Bob', 'HR'),
(3, 'Charlie', 'Sales');

CREATE PROCEDURE GetEmployeesByDepartment (IN dept_name VARCHAR(50))
-- Add the procedure body with BEGIN, SELECT, and END
# Your code here

Need a hint?

Use BEGIN and END to wrap the procedure body. The SELECT uses the input parameter dept_name.

4

Complete the procedure with delimiter if needed

If your SQL environment requires, add delimiter commands before and after the procedure to allow the semicolon inside the procedure body. For example, add DELIMITER $$ before and DELIMITER ; after the procedure definition.

SQL

DELIMITER $$
CREATE TABLE Employees (
    EmployeeID INT,
    Name VARCHAR(100),
    Department VARCHAR(50)
);

INSERT INTO Employees (EmployeeID, Name, Department) VALUES
(1, 'Alice', 'Sales'),
(2, 'Bob', 'HR'),
(3, 'Charlie', 'Sales');

CREATE PROCEDURE GetEmployeesByDepartment (IN dept_name VARCHAR(50))
BEGIN
    SELECT * FROM Employees WHERE Department = dept_name;
END$$
DELIMITER ;
# Your code here

Need a hint?

Use DELIMITER $$ before and DELIMITER ; after the procedure to handle semicolons inside the procedure.