AI Features
Home
Courses
SQL Interview Preparation—Intermediate Level

Answer: Parameterized Stored Procedures

Find a detailed explanation of creating parameterized stored procedure in SQL.

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 ...

Ask