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

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

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

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

Hint

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

Practice

1. What is the main purpose of the CREATE PROCEDURE statement in SQL?

easy

A. To display data from a table

B. To delete data from a table

C. To create a new database

D. To save a set of SQL commands that can be run later

Solution

1. Step 1: Understand the purpose of CREATE PROCEDURE

   The CREATE PROCEDURE statement is used to save a group of SQL commands as a procedure that can be executed later.

2. Step 2: Compare options with the purpose

   Options B, C, and D describe other SQL commands, not the purpose of CREATE PROCEDURE.

3. Final Answer:

   To save a set of SQL commands that can be run later -> Option D

4. Quick Check:

   CREATE PROCEDURE saves commands = A [OK]

Hint: Remember: procedures store commands to run later [OK]

Common Mistakes:

• Confusing CREATE PROCEDURE with CREATE TABLE
• Thinking it runs commands immediately
• Mixing it up with SELECT statement

2. Which of the following is the correct basic syntax to create a procedure named GetUsers without parameters?

easy

A. CREATE PROCEDURE GetUsers AS SELECT * FROM Users;

B. CREATE PROCEDURE GetUsers BEGIN SELECT * FROM Users; END;

C. CREATE PROCEDURE GetUsers() BEGIN SELECT * FROM Users; END;

D. CREATE PROCEDURE GetUsers SELECT * FROM Users;

Solution

1. Step 1: Recall standard procedure syntax

   The correct syntax includes parentheses for parameters (empty if none), and a BEGIN...END block to enclose statements.

2. Step 2: Check each option

   CREATE PROCEDURE GetUsers() BEGIN SELECT * FROM Users; END; correctly uses CREATE PROCEDURE GetUsers() BEGIN SELECT * FROM Users; END;. Options A, C, and D miss parentheses or BEGIN...END or have syntax errors.

3. Final Answer:

   CREATE PROCEDURE GetUsers() BEGIN SELECT * FROM Users; END; -> Option C

4. Quick Check:

   Procedure syntax needs () and BEGIN...END = B [OK]

Hint: Always use () and BEGIN...END in procedure syntax [OK]

Common Mistakes:

• Omitting parentheses after procedure name
• Not using BEGIN and END to wrap statements
• Missing semicolon inside procedure

3. Given the procedure below, what will be the output when calling CALL GetUserCount();?

CREATE PROCEDURE GetUserCount()
BEGIN
  SELECT COUNT(*) AS TotalUsers FROM Users;
END;

medium

A. No output because procedure does not return anything

B. The total number of rows in the Users table

C. Syntax error because of missing parameters

D. An error because SELECT cannot be used in procedures

Solution

1. Step 1: Understand what the procedure does

   The procedure runs a SELECT COUNT(*) query on the Users table and returns the count as TotalUsers.

2. Step 2: Analyze the CALL statement output

   Calling CALL GetUserCount(); executes the procedure and returns the count of rows in Users table as a result set.

3. Final Answer:

   The total number of rows in the Users table -> Option B

4. Quick Check:

   CALL runs procedure and returns SELECT result = C [OK]

Hint: CALL runs procedure and returns SELECT results [OK]

Common Mistakes:

• Thinking procedures cannot return SELECT results
• Assuming parameters are always required
• Confusing procedure call with function call

4. Identify the error in the following procedure definition:

CREATE PROCEDURE AddUser(
  IN username VARCHAR(50),
  IN email VARCHAR(100)
)
INSERT INTO Users (Username, Email) VALUES (username, email);
END;

medium

A. Missing BEGIN before the INSERT statement

B. Parameters should not have IN keyword

C. END keyword is not needed

D. Procedure name cannot have parameters

Solution

1. Step 1: Check procedure block structure

   Procedures must enclose statements inside BEGIN and END blocks. This code misses BEGIN before INSERT.

2. Step 2: Verify other syntax parts

   IN keyword for parameters is correct, END is needed, and procedure names can have parameters.

3. Final Answer:

   Missing BEGIN before the INSERT statement -> Option A

4. Quick Check:

   Procedures need BEGIN...END blocks = A [OK]

Hint: Always wrap procedure statements with BEGIN and END [OK]

Common Mistakes:

• Forgetting BEGIN before statements
• Removing IN keyword from parameters
• Omitting END keyword

5. You want to create a procedure UpdateUserEmail that takes a user ID and a new email, then updates the user's email in the Users table. Which of the following is the correct complete procedure?

hard

A. CREATE PROCEDURE UpdateUserEmail(IN userId INT, IN newEmail VARCHAR(100)) BEGIN UPDATE Users SET Email = newEmail WHERE Id = userId; END;

B. CREATE PROCEDURE UpdateUserEmail(userId INT, newEmail VARCHAR(100)) UPDATE Users SET Email = newEmail WHERE Id = userId;

C. CREATE PROCEDURE UpdateUserEmail(IN userId INT, IN newEmail VARCHAR(100)) UPDATE Users SET Email = newEmail WHERE Id = userId; END;

D. CREATE PROCEDURE UpdateUserEmail(IN userId INT, IN newEmail VARCHAR(100)) BEGIN UPDATE Users SET Email = 'newEmail' WHERE Id = userId; END;

Solution

1. Step 1: Check parameter declaration and statement block

   Parameters must have IN keyword and the statements must be inside BEGIN...END.

2. Step 2: Verify the UPDATE statement correctness

   CREATE PROCEDURE UpdateUserEmail(IN userId INT, IN newEmail VARCHAR(100)) BEGIN UPDATE Users SET Email = newEmail WHERE Id = userId; END; correctly uses parameters without quotes around newEmail, so it updates with the variable value. CREATE PROCEDURE UpdateUserEmail(IN userId INT, IN newEmail VARCHAR(100)) BEGIN UPDATE Users SET Email = 'newEmail' WHERE Id = userId; END; incorrectly uses quotes, making it a string literal.

3. Final Answer:

   CREATE PROCEDURE UpdateUserEmail(IN userId INT, IN newEmail VARCHAR(100)) BEGIN UPDATE Users SET Email = newEmail WHERE Id = userId; END; -> Option A

4. Quick Check:

   Parameters with IN and BEGIN...END, no quotes on variables = D [OK]

Hint: Use IN parameters and BEGIN...END; no quotes on variables [OK]

Common Mistakes:

• Omitting BEGIN...END block
• Forgetting IN keyword for parameters
• Using quotes around parameter variables in SQL