How to use Table output from stored MYSQL Procedure

by

This can’t be done, directly, because the output of an unbounded select in a stored procedure is a result set sent to the client, but not technically a table.

The workaround is to let the proc put the data in a temporary table after creating the table for you. This table will be available only to your connection when the procedure finishes. It will not cause a conflict if somebody else runs the proc at the same time and won’t be visible to any other connection.

Add this to the procedure:

DROP TEMPORARY TABLE IF EXISTS foo;
CREATE TEMPORARY TABLE foo SELECT ... your existing select query here ...;

When your procedure finishes, SELECT * FROM foo; will give you what you what you would have gotten from the proc. You can join to it pretty much like any table.

When you’re done, drop it, or it will go away on its own when you disconnect. If you run the proc again, it will be dropped and recreated.

More Related Contents:

  1. SQL query return data from multiple tables
  2. quick selection of a random row from a large table in mysql
  3. MySQL SELECT only not null values
  4. Stored procedure that Automatically delete rows older than 7 days in MYSQL
  5. Pass array to MySQL stored routine
  6. Combine Multiple child rows into one row MYSQL
  7. MySQL stored procedures use them or not to use them
  8. MySQL SELECT increment counter
  9. MySQL create stored procedure syntax with delimiter
  10. How to get an age from a D.O.B field in MySQL?
  11. Writing optional parameters within stored procedures in MySQL? [duplicate]
  12. How does MySQL’s ORDER BY RAND() work?
  13. MySQL #1093 – You can’t specify target table ‘giveaways’ for update in FROM clause
  14. mysql stored procedure that calls itself recursively
  15. How do you debug MySQL stored procedures?
  16. MySQL How do you INSERT INTO a table with a SELECT subquery returning multiple rows?
  17. MySQL INSERT INTO … VALUES and SELECT
  18. MySQL error 1241: Operand should contain 1 column(s)
  19. How can I use transactions in my MySQL stored procedure?
  20. Compare only day and month with date field in mysql
  21. mysql select sum group by date
  22. use a variable for table name in mysql sproc
  23. MySQL – SQL_BIG_SELECTS
  24. Split a string and loop through values in MySQL stored procedure
  25. MySQL INNER JOIN select only one row from second table
  26. MySQL Select Query – Get only first 10 characters of a value
  27. My SQL Dynamic query execute and get ouput into a variable in stored procedure
  28. What is the default MySQL JOIN behaviour, INNER or OUTER?
  29. What is dynamic SQL?
  30. MySQL cartesian product between two SELECT statements

Leave a Comment