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@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
>>
>>
> //
>
>/
>/
>------------------------------------------------------------------------
>/
>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
