Home Stored Procedures Select Wrapper procedure to load specific data

Wrapper procedure to load specific data

Procedure Name:
load_team_member_wrapper
Procedure type:
Select
Input variables:
One
Output:
None
Uses function:
SUBSTRING(), USER()

The purpose of this stored procedure is to demonstrate using nested functions,IF..ELSE logic to evaluate a string and calling another stored procedure from within a stored procedure.

Tables:

team and sport tables

Stored Procedure

DELIMITER $$
DROP PROCEDURE IF EXISTS `sportszone`.`load_team_member_wrapper` $$
CREATE PROCEDURE `sportszone`.`load_team_member_wrapper` (IN tm_nm VARCHAR(100))
BEGIN
DECLARE tm_id INT;
DECLARE usr_nm VARCHAR(100);
DECLARE today_dt DATETIME;
SET today_dt = now();
SET usr_nm = (select substring(user(), 1, 4));
SET tm_id = (Select team_id from team where team_name = tm_nm);
IF (select s.sport_name
from sport s
join team t
on s.sport_id = t.sport_id
where t.team_id = tm_id) = 'Baseball' THEN
CALL team_member_baseball_set (tm_id, usr_nm, today_dt);
ELSEIF (select s.sport_name
from sport s
join team t
on s.sport_id = t.sport_id
where t.team_id = tm_id) = 'Football' THEN
CALL team_member_football_set (tm_id, usr_nm, today_dt);
END IF;
END $$
DELIMITER ;

Call Stored Procedure

call load_team_member_wrapper('Chicago Cubs');

Result Set

None

Sample Data Used

load_baseball_players Table

The load_baseball_players table is a permanent temporary table used to stage data that will be loaded into the normalized tables. The method for loading this table uses a query browser window and LOAD DATA INFILE command. The raw data is stored in a .csv file.

load_baseball_players data
team_member Table
team member data

Last Updated ( Thursday, 27 August 2009 12:47 )

Who's Online
We have 66 guests online