Thanks. That fixed the problem. I put all of my DECLARE statements at the
beginning of the procedure. I finally got the thing to compile and it looks
like this:
DELIMITER $$
DROP PROCEDURE IF EXISTS `code_library`.`Search_Code_Samples` $$
CREATE [EMAIL PROTECTED] PROCEDURE `Search_Code_Samples`(
IN search_words VARCHAR(300)
, IN multi_word_condition VARCHAR(3)
, IN language_id INT
, IN sample_type CHAR(1)
)
READS SQL DATA
COMMENT 'Prepares and executes SQL to find records according to the
data'
BEGIN
DECLARE Base_SQL VARCHAR(1500);
DECLARE Filter_Clause VARCHAR(3000);
DECLARE Final_SQL VARCHAR(5500);
DECLARE First_Instance CHAR(1);
DECLARE len_search_words INT;
DECLARE word_idx INT;
DECLARE last_pos INT;
DECLARE word_length INT;
DECLARE new_word varchar(200);
DECLARE Search_Clause VARCHAR(1000);
DECLARE this_Word varchar(200);
DECLARE no_more_rows INT default 0;
DECLARE words_list CURSOR FOR SELECT Search_Word FROM Temp_Word_List;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = 1;
-- First handle the list of search words
IF (multi_word_condition IS NULL OR multi_work_condition = '' OR
multi_word_condition = ' ') THEN
SET multi_word_condition = 'AND';
END IF;
IF (sample_type IS NULL) OR (sample_type = ' ') THEN
SET sample_type = 'A';
END IF;
SET Base_SQL = 'select * from CodeSamples_View ';
SET Filter_Clause = '';
SET First_Instance = 'Y';
IF (search_words IS NOT NULL) AND (search_words <> '') AND (search_words <>
' ') THEN
-- Parse the list of search words by spaces
SET len_search_words = LENGTH(search_words);
SET word_idx = 1;
SET last_pos = 1;
CREATE TEMPORARY TABLE Temp_Word_List (Search_Word VARCHAR(200) NOT NULL);
WHILE (word_idx <= len_search_words) DO
IF (SUBSTRING(search_words, word_idx, 1) = ' ') THEN
SET word_length = word_idx - last_pos;
SET new_word = TRIM(SUBSTRING(search_words, last_pos, word_length));
SET last_pos = word_idx;
IF (new_word <> '') THEN
INSERT INTO Temp_Word_List VALUES (new_word);
END IF;
END IF;
SET word_idx = word_idx + 1;
END WHILE;
-- Put in the very last word
IF (word_idx > last_pos) THEN
SET word_length = word_idx - last_pos;
SET new_word = TRIM(SUBSTRING(search_words, last_pos, word_length));
IF (new_word <> '') THEN
INSERT INTO Temp_Word_List VALUES (new_word);
END IF;
END IF;
-- Generate the SQL clause for the search words.
OPEN words_list;
REPEAT
FETCH words_list INTO this_Word;
IF (no_more_rows <> 1) THEN
IF (First_Instance = 'Y') THEN
SET First_Instance = 'N';
ELSE
SET Filter_Clause = concat(Filter_Clause, multi_word_condition, ' ');
END IF;
SET Filter_Clause = concat(Filter_Clause, 'match(title,description)
against(''', this_Word, ''') ');
END IF;
UNTIL (no_more_rows = 1) END REPEAT;
CLOSE words_list;
DROP TEMPORARY TABLE Temp_Word_List;
END IF;
-- Language Id
IF (language_id IS NOT NULL AND language_id > 0) THEN
IF (First_Instance = 'Y') THEN
SET First_Instance = 'N';
ELSE
SET Filter_Clause = concat(Filter_Clause, ' AND ');
END IF;
SET Filter_Clause = concat(Filter_Clause, 'language = ', cast(language_id
as CHAR(4)), ' ');
END IF;
-- Sample type
IF (sample_type IS NOT NULL AND sample_type <> 'A' AND sample_type <> ''
AND sample_type <> ' ') THEN
IF (First_Instance = 'Y') THEN
SET First_Instance = 'N';
ELSE
SET Filter_Clause = concat(Filter_Clause, ' AND ');
END IF;
SET Filter_Clause = concat(Filter_Clause, 'sample_type = ''', sample_type,
'''');
END IF;
-- Construct the final SQL statement.
IF (Filter_Clause = '') THEN
SET @Final_SQL = Base_SQL;
ELSE
SET @Final_SQL = concat(Base_SQL, 'WHERE ', Filter_Clause);
END IF;
-- Execute the constructed SQL Statement.
PREPARE search_statement FROM @Final_SQL;
EXECUTE search_statement;
DEALLOCATE PREPARE search_statement;
END $$
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
