Below is a new stored procedure that I am trying to make for building a SQL
with the supplied parameters.
1:DELIMITER $$
2:CREATE PROCEDURE `Search_Code_Samples`(
3: IN search_words VARCHAR(300)
4: , IN multi_word_condition VARCHAR(3)
5: , IN language_id INT
6: , IN sample_type CHAR(1)
7:)
8: READS SQL DATA
9: COMMENT 'Prepares and executes SQL to find records according to the
data provided.'
10:BEGIN
11: DECLARE Base_SQL VARCHAR(1500);
12: DECLARE Filter_Clause VARCHAR(3000);
13: DECLARE Final_SQL VARCHAR(5500);
14: DECLARE First_Instance CHAR(1);
15: DECLARE len_search_words INT;
16: DECLARE word_idx INT;
17: DECLARE last_pos INT;
18: DECLARE word_length INT;
19: DECLARE new_word varchar(200);
20: DECLARE Search_Clause VARCHAR(1000);
21: DECLARE this_Word varchar(200);
22: DECLARE no_more_rows INT default 0;
23:
24: -- First handle the list of search words
25: IF (multi_word_condition IS NULL OR multi_work_condition = '' OR
multi_word_condition = ' ') THEN
26: SET multi_word_condition = 'AND';
27: END IF;
28: IF (sample_type IS NULL) OR (sample_type = ' ') THEN
29: SET sample_type = 'A';
30: END IF;
31: SET Base_SQL = 'select * from CodeSamples_View ';
32: SET Filter_Clause = '';
33: SET First_Instance = 'Y';
34: IF (search_words IS NOT NULL) AND (search_words <> '') AND
(search_words <> ' ') THEN
35: -- Parse the list of search words by spaces
36: SET len_search_words = LENGTH(search_words);
37: SET word_idx = 1;
38: SET last_pos = 1;
39: CREATE TEMPORARY TABLE Temp_Word_List (Search_Word VARCHAR(200)
NOT NULL);
40: WHILE (word_idx <= len_search_words) DO
41: IF (SUBSTRING(search_words, word_idx, 1) = ' ') THEN
42: SET word_length = word_idx - last_pos;
43: SET new_word = TRIM(SUBSTRING(search_words, last_pos,
word_length));
44: SET last_pos = word_idx;
45: IF (new_word <> '') THEN
46: INSERT INTO Temp_Word_List VALUES (new_word);
47: END IF;
48: END IF;
49: SET word_idx = word_idx + 1;
50: END WHILE;
51: -- Put in the very last word
52: IF (word_idx > last_pos) THEN
53: SET word_length = word_idx - last_pos;
54: SET new_word = TRIM(SUBSTRING(search_words, last_pos,
word_length));
55: IF (new_word <> '') THEN
56: INSERT INTO Temp_Word_List VALUES (new_word);
57: END IF;
58: END IF;
59: -- Generate the SQL clause for the search words.
60: DECLARE words_list CURSOR FOR SELECT Search_Word FROM
Temp_Word_List;
61: DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = 1;
62: OPEN words_list;
63: REPEAT
64: FETCH words_list INTO this_Word;
65: IF (no_more_rows <> 1) THEN
66: IF (First_Instance = 'Y') THEN
67: SET First_Instance = 'N';
68: ELSE
69: SET Filter_Clause = concat(Filter_Clause,
multi_word_condition, ' ');
70: END IF;
71: SET Filter_Clause = concat(Filter_Clause,
'match(title,description) against(''', this_Word, ''') ');
72: END IF;
73: UNTIL (no_more_rows = 1) END REPEAT;
74: CLOSE words_list;
75: DROP TEMPORARY TABLE Temp_Word_List;
76: END IF;
77:
78: -- Language Id
79: IF (language_id IS NOT NULL AND language_id > 0) THEN
80: IF (First_Instance = 'Y') THEN
81: SET First_Instance = 'N';
82: ELSE
83: SET Filter_Clause = concat(Filter_Clause, ' AND ');
84: END IF;
85: SET Filter_Clause = concat(Filter_Clause, 'language = ',
cast(language_id as CHAR(4)), ' ');
86: END IF;
87:
88: -- Sample type
89: IF (sample_type IS NOT NULL AND sample_type <> 'A' AND sample_type
<> '' AND sample_type <> ' ') THEN
90: IF (First_Instance = 'Y') THEN
91: SET First_Instance = 'N';
92: ELSE
93: SET Filter_Clause = concat(Filter_Clause, ' AND ');
94: END IF;
95: SET Filter_Clause = concat(Filter_Clause, 'sample_type = ''',
sample_type, '''');
96: END IF;
97:
98: -- Construct the final SQL statement.
99: IF (Filter_Clause = '') THEN
100: SET Final_SQL = Base_SQL;
101: ELSE
102: SET Final_SQL = concat(Base_SQL, 'WHERE ', Filter_Clause);
103: END IF;
104:
105: -- Execute the constructed SQL Statement.
106: PREPARE search_statement FROM Final_SQL;
107: EXECUTE search_statement;
108:END; $$
When attempting to compile this procedure I get this error:
You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near 'DECLARE
this_Word varchar(200); DECLARE no_more_rows INT default 0; DECLA' at
line 59
This may indicate that there is something on or before line 59 that it does
not like, but I cannot figure it out. I have tried everything including
removal of the query and replacing the temporary table with a regular table.
Maybe another pair of eyes can help me find this syntax error. Your help is
greatly appreciated.
Thanks.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
