Stored Procedures for MySQL to execute search prompts with INOUT Parameters

Stored Procedures for MySQL to execute search prompts with INOUT Parameters

[SQL] DELIMITER $ USE 'b10k' $ DROP PROCEDURE IF EXISTS 'SP _ suggest '$ CREATE DEFINER = 'luth' @ '% 'Procedure 'SP _ suggest '( IN query_column VARCHAR (100 ), /* retrieved field name */IN keyword VARCHAR (100),/* keyword */IN table_name VARCHAR (100 ), /* Name of the retrieved table */INOUT result_count INT/* Number of matching records to be retrieved */) COMMENT execute keyword search 'in in/* Definition variable */DECLARE m_begin_row int default 0; DECLARE m_where_string CHAR (128); DECLARE m_order_string CHAR (128); DECLARE m_limit_string CHAR (64 ); /* construct the statement */SET m_begin_row = result_count; SET m_where_string = CONCAT ('where', query_column, 'like \ '', keyword, '% \''); SET m_order_string = CONCAT ('ORDER BY', query_column); SET m_limit_string = CONCAT ('limit', result_count); SET @ COUNT_STRING = CONCAT ('select distinct count (*) INTO @ ROWS_TOTAL FROM ', table_name, '', m_where_string,'', m_order_string, '', m_limit_string); SET @ MAIN_STRING = CONCAT ('select DISTINCT', query_column, 'from', table_name, '', m_where_string,'', m_order_string, '', m_limit_string);/* preprocessing */PREPARE count_stmt FROM @ COUNT_STRING; EXECUTE count_stmt; deallocate prepare count_stmt; SET result_count = @ ROWS_TOTAL; PREPARE main_stmt FROM @ MAIN_STRING; EXECUTE main_stmt; deallocate prepare main_stmt; END $ DELIMITER;/* call */SET @ aa = 10; CALL sp_suggest ('Latin _ name', 'A', 'species ', @ aa); SELECT @ aa;