List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:April 1 2005 5:02pm
Subject:Re: Need help with Stored Procedures iin MySQL 5.0.3-beta
View as plain text  
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
Thread
Need help with Stored Procedures iin MySQL 5.0.3-betaDinesh Anchan1 Apr
  • Re: Need help with Stored Procedures iin MySQL 5.0.3-betaPeter Brawley1 Apr
RE: Need help with Stored Procedures iin MySQL 5.0.3-betaDinesh Anchan1 Apr
  • Re: Need help with Stored Procedures iin MySQL 5.0.3-betaPeter Brawley1 Apr