Home Stored Procedures Insert and Update Insert Procedure using a CURSOR

Insert Procedure using a CURSOR

Procedure Name:
team_member_baseball_set
Procedure type:
Insert
Input variables:
Three
Output:
None
Uses function:
SELECT LAST_INSERT_ID()

The purpose of this stored procedure is to demonstrate using a CURSOR to evaluate and insert data into multiple tables.

Tables:

team_member and position tables

Stored Procedure

DELIMITER $$
DROP PROCEDURE IF EXISTS `sportszone`.`team_member_baseball_set` $$
CREATE PROCEDURE `sportszone`.`team_member_baseball_set` (IN tm_id INT, IN usr_nm VARCHAR(25), IN today_dt DATETIME)
BEGIN
DECLARE new_team_member_id INT;
DECLARE pos_id INT DEFAULT 0;
DECLARE plyr_stt_id INT DEFAULT 0;
DECLARE done INT DEFAULT 0;
DECLARE pos_abbrv INT;
DECLARE numbr INT;
DECLARE frst VARCHAR(45);
DECLARE lst VARCHAR(50);
DECLARE hght VARCHAR(5);
DECLARE wght VARCHAR(3);
DECLARE dtofbrth DATE;
DECLARE stts VARCHAR(3);
DECLARE player_cur CURSOR FOR
select position_abbrev, number, first, last, HT, WT, DOB, status
from load_baseball_players;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN player_cur;
REPEAT
FETCH player_cur INTO pos_abbrv, numbr, frst, lst, hght, wght, dtofbrth, stts;
IF NOT done THEN
SET plyr_stt_id = (Select player_status_id from player_status where status_name = stts);
Insert team_member (team_id,
team_member_number,
first_name,
last_name,
date_of_birth,
height,
weight,
player_status_id,
insert_date,
insert_by,
modified_date,
modified_by)
values (tm_id,
numbr,
frst,
lst,
dtofbrth,
hght,
wght,
plyr_stt_id,
today_dt,
usr_nm,
today_dt,
usr_nm);
SET new_team_member_id = (SELECT LAST_INSERT_ID());
SET pos_id = (Select position_id from position where position_abbreviation = pos_abbrv);
IF pos_id > 0 THEN
Insert team_member_position (team_member_id, position_id)
Values (new_team_member_id, pos_id);
ELSEIF pos_id = 0 THEN
#invalid position in load table - remove team member
Delete from team_member where team_member_id = new_team_member_id;
#wound usually add additional code for error handling here!!!
END IF;
Set pos_id = 0;
END IF;
UNTIL done END REPEAT;
END $$
DELIMITER ;

Call Stored Procedure

call team_member_baseball_set(1, 'admin', now());

Result Set

Sample Data Used

load_baseball_players Table
load_baseball_players data
team_member Table
team member data
Who's Online
We have 42 guests online