Search⌘ K
Home
Courses
SQL Interview Preparation—Intermediate Level

Answer: Parameterized Stored Procedures

Explore how to define and call parameterized stored procedures that allow dynamic input values to be passed at runtime. Understand the use of DELIMITER, procedure creation, and execution to efficiently filter and retrieve data. Learn alternate approaches such as prepared statements and views, and deepen your SQL skills for intermediate interview scenarios.

We'll cover the following...

Solution

The solution is given below:

MySQL
-- Query  with stored procedure named GetSalesByCategoryAndMonth 
-- having parameters for categoryID and month
DELIMITER $$
CREATE PROCEDURE GetSalesByCategoryAndMonth(IN catID INT, 
                                            IN salesMonth VARCHAR(20))
BEGIN
    SELECT *
    FROM   Sales S
    WHERE  S.CategoryID = catID AND S.Month = salesMonth;
END $$
DELIMITER ;
-- Execute the stored procedure
CALL GetSalesByCategoryAndMonth (3 , 'March');

Code explanation

The explanation of the solution code is given below:

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

  • Lines 5–6: The CREATE PROCEDURE defines a stored procedure called GetSalesByCategoryAndMonth with two parameters: catID and salesMonth.

  • Lines 7–11: The BEGIN and END are used to define the body of the stored procedure. The SELECT statement retrieves the data from the Sales table. The WHERE clause filters the record(s) with respect to the specified condition.

  • Line 13: The DELIMITER ; resets the statement delimiter back to the ...