Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation—Intermediate Level

Answer: Creating a Stored Procedure

Understand how to create stored procedures in SQL to fetch data efficiently from tables. Learn the syntax, usage of delimiters, and how to execute procedures. Explore alternative methods like prepared statements, views, and common table expressions to achieve similar results. This lesson also includes practice problems and quizzes to reinforce your knowledge.

We'll cover the following...

Solution

The solution is given below:

MySQL
-- Query with stored procedure named as GetAllEmployees
DELIMITER $$
CREATE PROCEDURE GetAllEmployees ()
BEGIN
    SELECT * FROM Employees;
END $$
DELIMITER ;
-- Execute the stored procedure
CALL GetAllEmployees ();
-- Uncomment the following statement to list all stored procedures in the database
-- SHOW PROCEDURE STATUS WHERE DB = 'ProductSalesDB';

Code explanation

The explanation of the solution code is given below:

  • Line 2: The DELIMITER $$ changes the statement delimiter to $$ so semicolons can be used within the procedure.

  • Line 4: The CREATE PROCEDURE defines a stored procedure called GetAllEmployees.

  • Lines 5–7: The BEGIN and END are used to define the body of the stored procedure. ...