SQL Interview Preparation—Intermediate Level/

...

/

Answer: Creating a Stored Procedure

Answer: Creating a Stored Procedure

Find a detailed explanation for creating a stored procedure in SQL.

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. The SELECT statement fetches all the records in the Employees table.

  • Line 9: The DELIMITER ; resets the statement delimiter back to the default semicolon (;).

  • ...