Home Stored Procedures Select "Select" Stored Procedure using a JOIN statement

"Select" Stored Procedure using a JOIN statement

Procedure Name:
team_get

Procedure type:
Select

Input variables:
One

Output:
List result set

Uses function:
none

The purpose of this stored procedure is to demonstrate using a single input variable, a JOIN statement and returning a result set.

Tables:

team_type, team and sport tables

Stored Procedure

DELIMITER $$
DROP PROCEDURE IF EXISTS `team_get` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `team_get`(IN tm_id INT)
BEGIN
/*****************************************
Spoc Name: team_get
Date created: 07/23/2009
Author: tcblazek
Description: Returns information for a single team in the team table
Input variables: team_id from the team table
Output: a single row result from the team table
*****************************************/

Select
t.team_name,
t.team_abbreviation,
t.team_description,
tt.team_type_name,
s.sport_name
FROM team t
join team_type tt
on t.team_type_id = tt.team_type_id
join sport s
on t.sport_id = s.sport_id
WHERE t.team_id = tm_id;
END $$
DELIMITER ;

Call Stored Procedure

l team_get(2);

Result Set

Results of sproc call

Sample Data Used

Team_type Table
team type data
Team Table
team data
Sport Table
team data
Who's Online
We have 23 guests online