SQL Interview Preparation—Intermediate Level/

...

/

Answer: Parameterized Stored Procedures

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