RE: Need help with Stored Procedures iin MySQL 5.0.3-beta

[Dinesh Anchan]

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@stripped] 
Sent: Friday, April 01, 2005 12:03 PM
To: Anchan, Dinesh
Cc: mysql@stripped
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