how to write stored procedures in mysql?

[shivapadma]

1. i want to write a stored procedure using mysql 5.0.45 version.

2. when i tried with the following code ,i am unable to write

mysql->delimeter//
         ->create procedure proc(out p1 int)
         ->select count(*) into p1 from emp;
 

when i pressed enter, the following error is appeared .

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 'delim
eter//
create procedure proc(out p1 int)
select count(*) into p1 from emp' at line 1
 

3.i even checked the manual of mysql 5.0 ver
the syntax is right


please,someone help me in this problem....

[Atli]

Hi.

That particular problem would be that there is no space between the DELIMITER command and the parameter you are passing it.

I would also guess that the fact you didn't end the procedure with a END would cause a problem later on.

Check out the correct syntax for stored procedures in the manual. (The two examples right above the user comments show it very well.)

[shivapadma]

when i tried with the following code the procedure got executed

mysql->create procedure proc(inout n int,out p int)
set m=n+1;

mysql->set @n=5;
mysql->call proc(@n,@m);
mysql->select @n,@m;

the output is @n @m
5 6


but when iam using begin statement with the following code,i am getting error

mysql->create procedure proc(inout n int m,out p int)
->begin
->set m=n+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 'out p
int' at line 2


if someone knows what is the error please,kindly help me....

[amitpatel66]

This should help!! .