Stored Procedures Exercise

Part One of this exercise will show you how to:

• Create a simple stored procedure.
• Execute the stored procedure.

Directions

To create a stored procedure called prGetCustomers that simply returns all rows in the Customers table:

1. Locate or create the Northwinds database.
2. Open PhpMyAdmin (or work from the command line interface).
3. Be sure to select the Northwinds database from the left panel, or preface each of the following statements with the statement "USE northwind;"
4. Type the following

   DELIMITER //
   CREATE PROCEDURE prGetCustomers ()
     BEGIN
       SELECT * FROM customers;
     END //
   DELIMITER ;

5. Now, execute this stored procedure. Type

   CALL prGetCustomers ();

6. The results will be displayed in the lower window.
7. Click here for a Getting Started with MySQL Stored Procedures tutorial.

Part Two of this exercise requires you to practice passing parameters to a stored procedure.

1. Enter the following code:

   DELIMITER //
   CREATE PROCEDURE prGetCustomersFromCountry (IN countryName VARCHAR(15))
     BEGIN
       SELECT CompanyName
       FROM customers
       WHERE Country = countryName;
     END //
   DELIMITER ;

2. Now, execute this stored procedure. Type

   CALL prGetCustomersFromCountry ('Canada');

3. Practice writing CountOrderByStatus and Capitalize from the Stored Procedure Parameters tutorial. Note that there is a link to their database in Step 4 of the Start Here tab, or you can download it here.

Play around with the other stored procedures features at MySQL Stored Procedures tutorial.