
Drop table if exists Player;
Drop table if exists Team;

-- create Team table with surrogate key
create table Team (
   tid INT UNSIGNED auto_increment PRIMARY KEY,
      -- makes it easier to refer to a team
   name varchar(30) UNIQUE
   ) engine=innodb;

delimiter ;
drop procedure if exists addPDemo;

-- demo of how to declare and use a local variable
-- this example assumes you used a surrogate key for the 
-- Team table

delimiter //
CREATE PROCEDURE addPDemo(
	teamName VARCHAR(45),
	pnumb INT UNSIGNED,
	pname VARCHAR(45))
BEGIN
	-- variable to hold team id for teamName
	-- assumes there is no team with id 0
	declare teamID INT UNSIGNED default 0;

	-- declare handler for duplicate insert into Player table here

	-- get TeamID for teamName given
	select tid into teamID
	from Team 
	where name = teamName;

	-- if team doesn't exist
	if (teamID = 0) THEN
      select concat('?Team does not exist: ', teamName,
			' when trying to add player: ',
			cast(pnumb as char));
	end if;
end
//
delimiter ;

insert into Team (name) values
	('Sand Crabs'),
	('Other team');

call addPDemo('rockets',11,'jimbob');


