Dinesh,
Do you have something like
DELIMITER |
before the CREATE PROCEDURE call, and
|
DELIMITER ;
after it?
Peter Brawley
http://www.artfulsoftware.com
-----
Anchan, Dinesh wrote:
>Hi,
>
>I am looking to migrate an Informix database to MySQL and trying to
>write a simple stored procedure in MySQL 5.0.3. I referred to few
>examples posted and used the statements from them but i get errors while
>creating this procedure.
>
>CREATE PROCEDURE sp_test
>(
> IN user_id integer,
> IN seg_id integer
>)
>
>BEGIN
>
> declare p_rows int;
> set p_rows = 0;
>
> select count(*)
> into p_rows
> from test
> where user_id = user_id
> and seg_id = seg_id;
>
> if p_rows = 1 then
> update test
> set visits = visits+1
> where user_id = user_id
> and seg_id = seg_id;
> else
> INSERT INTO test
> VALUES (user_id,seg_id, 1);
> end if;
>
>END
>
>ERROR:
>ERROR 1064 (42000): 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 '' at line 7
>ERROR 1193 (HY000): Unknown system variable 'p_rows'
>ERROR 1327 (42000): Undeclared variable: p_rows
>ERROR 1064 (42000): 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 'if p_rows = 1 then
> update test
> set visits = visits+1
> wher' at line 1
>ERROR 1064 (42000): 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 'else
> INSERT INTO test
> VALUES (user_id,seg_id, 1)' at line 1
>ERROR 1064 (42000): 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 'end if' at line 1
>ERROR 1064 (42000): 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 'END' at line 1
>
>Any help would be appreciated.
>
>Thanks
>
>Dinesh
>
>
>
>
>
>
>------------------------------------------------------------------------
>
>No virus found in this incoming message.
>Checked by AVG Anti-Virus.
>Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 4/1/2005
>
>
Attachment: [text/html]
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 4/1/2005
