From: Price, Randall (randallpvt.edu)
Date: Mon Aug 13 2007 - 14:14:35 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Tom,

I think the problem might be that you have to put all the DECLARE
statements at the top before the other statements. It seems like I had
a similar problem with this once.

Hope this helps.

Randall Price

Secure Enterprise Technology Initiatives
Microsoft Implementation Group
Virginia Tech Information Technology
1700 Pratt Drive
Blacksburg, VA 24060

Email: Randall.Pricevt.edu
Phone: (540) 231-4396
-----Original Message-----
From: Tom Khoury [mailto:twaksyahoo.com]
Sent: Monday, August 13, 2007 2:38 PM
To: mysqllists.mysql.com
Subject: Syntax Error in Stored Procedure

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/mysql?unsub=Randall.Pricevt.edu

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]