Home Stored Procedures Select "Select" Stored Procedure using the PREPARE statement

"Select" Stored Procedure using the PREPARE statement

Procedure Name:
teams_get

Procedure type:
Select

Input variables:
Three

Output:
Result set dependant on input variables

Uses function:
Concat()

The purpose of this stored procedure is to demonstrate using the PREPARE statement and the usage of LIMIT and OFFSET, useful in pagination. The LIMIT clause is used to limit the number of results returned in the SQL statement. If you have 100 rows in a table, and only want to return the first 10 you pass the number 10 into the procedure in the Limit variable. If you then want to show results 11-20, pass the number 10 in the OFFSET variable.

Tables:

team table

Stored Procedure

DELIMITER $$
DROP PROCEDURE IF EXISTS `teams_get`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `teams_get`(IN orderby TEXT, IN row_count INT, IN off_set INT)
BEGIN
/*****************************************
Spoc Name: teams_get
Date created: 07/23/2009
Author: tcblazek
Description: Returns a list of teams
Input variables: orderby used to order the rows, row_count used to determine the # of rows returned and off_set
used to return the next set of data
Output: multiple rows of data determined by row count
*****************************************/
SET @s = CONCAT('SELECT team_id,
team_abbreviation,
team_name,
team_description
FROM team
ORDER BY ', orderby,
' LIMIT ', row_count, ' OFFSET ', off_set);

PREPARE stmnt FROM @s;
EXECUTE stmnt;
DEALLOCATE PREPARE stmnt;
END $$
DELIMITER ;

Call Stored Procedure

call teams_get ('team_name', 2, 0);
call teams_get ('team_name', 2, 2);
call teams_get ('team_name', 2, 4);

Result Set

Results of sproc call Results of sproc call Results of sproc call

Sample Data Used

Team Table
team data
Who's Online
We have 16 guests online