What I do is prepare the CREATE PROC script in a text editor template which starts with
  DELIMITER |
and ends with
  |
  DELIMITER ;

PB

-----

Anchan, Dinesh wrote:
Yes it is the delimiter which was giving the errors.  I had tried putting those statements inside the script, which didn't work.  But when i set it from the command line before executing the script to create the procedure it worked.  I am still not clear on how to use delimiter without much pain but i will get there.
 
Thanks for your help.
 
Dinesh


From: Peter Brawley [mailto:peter.brawley@earthlink.net]
Sent: Friday, April 01, 2005 12:03 PM
To: Anchan, Dinesh
Cc: mysql@lists.mysql.com
Subject: Re: Need help with Stored Procedures iin MySQL 5.0.3-beta

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


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