i want to create a stored procedure which will have 2 IN and 1 OUT parameter,
i want to pass username and password and want to get message [authenticate or not]
my stored procedure syntax look like-

DELIMITER $$
DROP PROCEDURE IF EXISTS `zero`.`user_varify`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `user_varify`(IN user_name VARCHAR(15),IN pass_word VARCHAR(15), OUT message VARCHAR(100))
BEGIN
DECLARE temp_password VARCHAR(15);
SELECT usr_pwd INTO temp_password from account_db where usr_name =user_name;
IF temp_password = pass_word THEN
SET message ='u r authenticated';
END IF;
IF temp_password != pass_word THEN
SET message='u r not authenticated';
END IF;
END$$
DELIMITER ;

to run this i wrote
CALL user_varify('amit','pathak',@message);
when i try to execute it ,i got a message at the bottom of query browser like- "query cancelled'
one thing is more when i add a line ----select message; in procedure after END IF, it is working properly, how it is happen-i want to know
1-right syntax is select @message but it is not working ,select message; it is working why
2-why i got the message like -"query cancelled"
3- i m using mysql query browser 1.2.12 here, i can execute one query at same time, for second i need to delete first, is there any solution of this..
I know i asked a lot, but really i spend my hours in this , when i failed then i posted my problem here.
please reply me
i got one thing more-> this is working fine in mysql text console....

Hi, I am not sure why you are not using a STORED FUNCTION for this type of query. You can return a tinyint 0 => false and 1 => true.

yes i can , but i m just a learner so i was trying - how to use stored procedure.....

There are several things about the script that you have written that may cause a problem. Firstly there is no exception handler defined. This can be a problem if the username does not exist.

I have put together a mini-tutorial which can be found here.

The better approach is to use a SELECT count(*) INTO variable. In this case you can compare both the username and password combination. If it matches then the variable will have a 1. If it does not then the variable will have the value 0.

The IF statement also has an ELSE part. You do not need to do 2 IF statements where the second is the inverse of the first.

good morning sir,
u are right, this is a right code for that kind of problem..... i think you add this page over ur website for me.. u r so kind sir... thank you so much sir .....sir one thing is more, i wondered a lot and i found that to save date kind of value in database there are only one format in mysql yyyy/mm/dd , u r bound to save the value in this format but u can change into other format when u retrieve from database as u require , for this there are some mysql functions... is it true? or i need to work more on this topic

When INSERTing into the database you can use the function STR_TO_DATE function as follows:

If you want to convert the date into a string you should use the DATE_FORMAT function as follows: