Home Stored Procedures Select "Select" Stored Procedure using two Input Variables

"Select" Stored Procedure using two Input Variables

Procedure Name:
team_type_by_sport_count

Procedure type:
Select

Input variables:
Two

Output:
Returns single integer

Uses function:
count()

The purpose of this stored procedure is to demonstrate using two input variables, using a function within a procedure and returning a result set.

Tables:

team_type, team and sport tables

Stored Procedure

DELIMITER $$
DROP PROCEDURE IF EXISTS `team_type_by_sport_count`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `team_type_by_sport_count`(IN tm_typ_id INT, IN sprt_id INT )
BEGIN
/*****************************************
Spoc Name: team_type_by_sport_count
Date created: 07/23/2009
Author: tcblazek
Description: Returns the number of teams in team table for a team type and sport
Input variables: team_type_id from the team_type table and sport_id from the sport table
Output: a count of the number of teams for a specific sport in the table
*****************************************/
SELECT COUNT(*)
FROM team
where team_type_id = tm_typ_id
and sport_id = sprt_id;
END $$
DELIMITER ;

Call Stored Procedure

call team_type_by_sport_count(1, 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 10 guests online