3

I made a stored procedure with in parameter. When I call this procedure no error, no result is shown to me. What's wrong in this code?

 drop PROCEDURE if exists `getUsersByContact`;

 delimiter $$
 Create PROCEDURE `getUsersByContact`(
   IN contact1 VARCHAR(10)
 )
 BEGIN
     SELECT id,name FROM tbl_user WHERE contact = contact1;
END $$
delimiter ;

When I call like this

 call getUsersByContact('9999999999');

It doesn't return any rows and there is no error shown in phpmyadmin. However when I use an out parameter and call stored procedure and select that variable it worked fine. I also tried this link and this link.

Community
  • 1
  • 1
  • @usermesam0023 but when I use out parameter it shows me error `Result consisted of more than one row`, if more than one row exists in table with same contact no. –  Jan 11 '15 at 09:50
  • maybe it's a phpMyAdmin problem then. Did you try with the `mysql` command line or a different SQL client? –  Jan 11 '15 at 09:51
  • I guess then you don't have such a row in the database. Works fine for me: http://sqlfiddle.com/#!2/719f95/1 –  Jan 11 '15 at 10:17
  • What happens when you try to run it through the graphical tool in phpMyAdmin? The easiest way is to expand the Procedures link in the navigation pane and select your procedure there. – Isaac Bennetch Jan 24 '15 at 20:06

2 Answers2

0

When run query inside SP, This one will work!!

 SELECT id,name
  FROM tbl_user 
  WHERE contact = `contactname`;

But this one will not work.

SELECT id,name
 FROM tbl_user
 WHERE contact = contact1;

Here you can find the reason:http://mysqldb.bigresource.com/SELECT-INTO-returns-null-in-Stored-Procedure-Jx95y6IPJ.html

Ataboy Josef
  • 2,087
  • 3
  • 22
  • 27
  • What should I do in order to solve my problem? I just need all rows which has same mobile number (e.g. to check if they entered a fake contact number). I know how to do this in sql but I need to do it via stored procedure. –  Jan 11 '15 at 10:08
  • Take a look at this one: http://stackoverflow.com/questions/14900351/select-into-returns-null-in-stored-procedure – Ataboy Josef Jan 11 '15 at 10:10
  • May be you can use FUNCTION instead of PROCEDURE – Ataboy Josef Jan 11 '15 at 10:11
0

I'm adding my answer in believe that it may be useful to others.

I was struggling with the same problem but when I called this procedure in mysqlworkbench it worked like a charm. So may be this is a phpmyadmin's issue as @a_horse_with_no_name suggest.

RN Kushwaha
  • 2,081
  • 3
  • 29
  • 40