From: Peter Brawley Date: April 1 2005 5:02pm Subject: Re: Need help with Stored Procedures iin MySQL 5.0.3-beta List-Archive: http://lists.mysql.com/mysql/182013 Message-Id: <424D7EC2.5010205@earthlink.net> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=======AVGMAIL-424D7EC375B5=======" --=======AVGMAIL-424D7EC375B5======= Content-Type: multipart/alternative; boundary=------------060107000100050003090102 --------------060107000100050003090102 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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 > > --------------060107000100050003090102 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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

--------------060107000100050003090102-- --=======AVGMAIL-424D7EC375B5======= Content-Type: text/plain; x-avg=cert; charset=us-ascii Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Content-Description: "AVG certification" 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 --=======AVGMAIL-424D7EC375B5=======--