Sample Exercises on Stored Procedure

                                         1.)Create a stored procedure that will display office city as well as number of employees assigned     which is the same city with the employeeNumber entered.

Call  GetOffice(1002)

Output:

Office                   Number_of_employees

San Francisco     6

Answer:

DELIMITER $$

DROP PROCEDURE IF EXISTS `company`.`GetOffice` $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetOffice`(IN parameter1 integer)

BEGIN /* start of block */

Select city, count(employeeNumber) as Number_of_Employees from offices c,employees e

where c.officeCode=e.officeCode and c.officeCode=(Select officeCode from employees where employeeNumber=parameter1);/* statement */

END $$

DELIMITER ;




2.) Create a stored procedure that will display the customerName, contactlastname and contactfirstName of the customers by entering the employees firstname and lastname and whose creditlimit is greather than the creditlimit entered.

CALL GetCustomers('Gerard', 'Hernandez',12000);

Output:

Your Customer                                 Contactlastname             ContactFirstname

'Atelier graphique'                         , 'Schmitt',                                          'Carine '

'La Rochelle Gifts'                           , 'Labrune',                                         'Janine '

'Euro+ Shopping Channel',          'Freyre',                                              'Diego '

'Daedalus Designs Imports',       'Rancé',                                                                'Martine '

'Mini Caravy',                                    'Citeaux',                                           'Frédérique '

'Alpha Cognac',                                'Roulet',                                              'Annette '

'Auto Associés & Cie.',                  'Tonini',                                              'Daniel '

Answer:

DELIMITER $$

DROP PROCEDURE IF EXISTS `company`.`GetCustomers` $$

CREATE PROCEDURE `GetCustomers`(IN parameter1 varchar(15),parameter2 varchar(15), parameter3 integer)

BEGIN /* start of block */

Select customerName as YourCustomers, contactlastname,contactfirstname from customers c,employees e

where c.salesRepEmployeeNumber=e.employeeNumber and firstname=parameter1 and lastname=parameter2 and creditLimit>parameter3;/* statement */

END $$

DELIMITER ;