Stored procedure does not exist, even after creating it

Question

I am trying to create a mysql stored procedure . I have successfully created a procedure using the following code :

delimiter $$
CREATE PROCEDURE `myprocedure` (IN
 var1 DATE) 
BEGIN 
<---code--> 
END

And

SHOW CREATE PROCEDURE myprocedure

shows me the procedure I have created.

But the Call myprocedure(2011-05-31);

shows me the following error

#1305 - PROCEDURE db.myprocedure does not exist

db is database where I have created the procedure

What mistake am I doing?

Can anyone help me in this?

Tiffie

mysql version please?? MySQL 5.0 introduced Stored Procedures – Bernat

need the full name to the sproc? or are you referring to the correct database? :P – Largeminded

AM using MYSQL 5.1.30 and i have checked the database name ,where am referring correct database name. – Tiffie

Are you creating the stored procedure in the database you expect? – Pharmacy

Yes, i have created the procedure in correct database. – Tiffie

There is only one reason why it is happening first drop the procedure. Now use the database command first and then again write the procedure, if you don't use the use database command procedure is not associated with your required database – Nauseating

Answer 1

please check the following example paying particular attention to use of delimiters and quoting of date input parameters.

drop procedure if exists my_procedure;

delimiter #

create procedure my_procedure 
(
in p_start_date date
) 
begin

-- do something...
select p_start_date as start_date; -- end of sql statement

end# -- end of stored procedure block

delimiter ; -- switch delimiters again

call my_procedure('2011-01-31');

+------------+
| start_date |
+------------+
| 2011-01-31 |
+------------+
1 row in set (0.00 sec)

Farrow