10

I write a very simple stored procedure as below:

DROP PROCEDURE IF EXISTS GetAllTours;

DELIMITER // 

CREATE PROCEDURE GetAllTours() 

BEGIN 

SELECT * FROM tours; 

END // 

DELIMITER ;

When I use 

CALL GetAllTours();

from SQL query to run this procedure, there is no output.

But if I run from >>Routines>>Execute, the same procedure, there will have output successfully.

Can anybody tell me how to run from SQL query and get the results?

Ravinder Reddy
  • 23,692
  • 6
  • 52
  • 82
user3583479
  • 101
  • 1
  • 1
  • 3

2 Answers2

3

You can just use this query: CALL GetAllTours

Werdo
  • 86
  • 5
-2

First of all, i think you are trying to create a view. Take a look here: http://dev.mysql.com/doc/refman/5.0/en/create-view.html

mysql> CREATE VIEW GetAllActiveTours AS SELECT * FROM tours where active=1;
mysql> select * from GetAllActiveTours;

for returning data from a procedure use an OUT parameter. http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html

example from link above:

mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
    -> BEGIN
    ->   SELECT COUNT(*) INTO param1 FROM t;
    -> END//
mysql> CALL simpleproc(@a);
mysql> SELECT @a;
nvanesch
  • 2,540
  • 14
  • 25
  • This is a command-line test. The OP was about calling from phpMyAdmin. – texnic Oct 04 '15 at 22:02
  • Great style to give -1 more than a year later because you did not read the question properly... OP asked: 'Can anybody tell me how to run from SQL query and get the results?' – nvanesch Oct 05 '15 at 11:18
  • He meant SQL query, that's a tab in phpMyAdmin. I am struggling with the same problem. – texnic Oct 05 '15 at 11:37
  • The main problem here is that a procedure is probably not what you want to fix this. Procedures are not meant to return sets of filtered data. Most likely you will want a view or a function (should you want to return a single scalar). take a look here: http://stackoverflow.com/questions/3744209/mysql-stored-procedure-vs-function-which-would-i-use-when Perhaps i can take a better look at your problem over chat to prevent pollution of this topic. – nvanesch Oct 05 '15 at 14:43