Hello.
Surround your code inside the IF statement with BEGIN...END. For example
this works for me:
drop procedure if exists testcursor;
DELIMITER //
CREATE PROCEDURE testcursor(IN city_name varchar(255))
DETERMINISTIC
BEGIN
declare tval int;
IF (INSTR(city_name, ',') > 0) THEN
begin
DECLARE cur_neighborhood CURSOR FOR
SELECT DISTINCT neighborhood
FROM listing
WHERE FIND_IN_SET(city, city_name);
open cur_neighborhood;
FETCH cur_neighborhood into tval;
close cur_neighborhood;
select tval;
end;
ELSE
begin
DECLARE cur_neighborhood CURSOR FOR
SELECT DISTINCT neighborhood
FROM listing WHERE city =city_name;
end;
select "goodby";
END IF;
END;
//
delimiter ;
call testcursor("test1,ts");
CREATE TABLE `listing` (
`neighborhood` varchar(255) default NULL,
`city` varchar(255) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin2
mysql> select * from listing;
+--------------+------+
| neighborhood | city |
+--------------+------+
| 1 | ts |
+--------------+------+
+------------------+
| version() |
+------------------+
| 5.0.16-debug-log |
+------------------+
Tripp Bishop wrote:
> Howdy all.
>
> I need to define a cursor in a stored procedure but I
> don't know what the nature of the select statement is
> going to be until runtime. I need to do something like
> the code below:
>
> IF (INSTR(city_name, ',') > 0) THEN
> DECLARE cur_neighborhood CURSOR FOR
> SELECT DISTINCT neighborhood FROM listing WHERE
> FIND_IN_SET(city, city_name);
> ELSE
> DECLARE cur_neighborhood CURSOR FOR
> SELECT DISTINCT neighborhood FROM listing WHERE
> city =
> city_name;
> END IF;
>
> MySQL doesn't like this syntax. Is there a way that I
> can accomplish this?
>
> Cheers,
>
> Tripp
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
