Home Stored Procedures Select "Select" Stored Procedure using an Input Variable

"Select" Stored Procedure using an Input Variable

Procedure Name:
team_type_count

Procedure type:
Select

Input variables:
One

Output:
Returns single integer

Uses function:
count()

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

Tables:

team_type and team tables

Stored Procedure

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

Call Stored Procedure

call team_type_count(1);

Result Set

Results of sproc call

Sample Data Used

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