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
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
|
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
