MySql stored procedures: How to select from procedure table?

mysql stored procedure example
how to view stored procedure in mysql workbench
mysql stored procedure set variable from select
stored procedure in mysql php
how to execute stored procedure in mysql
print value of variable in mysql procedure
mysql stored procedure return table
mysql stored procedure return multiple result set

Let's say we have a stored procedure selecting something from a table:

CREATE PROCEDURE database.getExamples() 
SELECT * FROM examples;

How can I use the result from this procedure in a later select? (I've tried

SELECT * FROM (CALL database.getExamples())

but with no success.) Should I use SELECT... INTO outVariable in the procedure? Or should I use a function returning the table instead?

Reformulated the question in this thread: Can a stored procedure/function return a table?. Obviously, it isn't possible without the use for temp tables.

Listing Stored Procedures from Databases in the MySQL Server, Listing stored procedures using SHOW PROCEDURE STATUS statement The routines table in the information_schema database contains all To show all stored procedures of a particular database, you use the following query: SELECT​  To show all stored procedures of a particular database, you use the following query: SELECT routine_name FROM information_schema.routines WHERE routine_type = 'PROCEDURE' AND routine_schema = '<database_name>'; For example, this statement lists all stored procedures in the classicmodels database: SELECT routine_name FROM information_schema.routines WHERE routine_type = 'PROCEDURE' AND routine_schema = 'classicmodels';

CREATE TABLE #TempTable
(OID int IDENTITY (1,1),
VAr1 varchar(128) NOT NULL,
VAr2 varchar(128) NOT NULL)

Populate temporary table

INSERT INTO #TempTable(VAr1 , VAr2 )
SELECT * FROM examples

MySQL stored procedures: all ways to produce an output, They cannot be returned or passed to another procedure, function or query. Tables. To return multiple values with a clear schema, we can use a  Stored Procedure. A procedure (often called a stored procedure) is a subroutine like a subprogram in a regular computing language, stored in database. A procedure has a name, a parameter list, and SQL statement(s). All most all relational database system supports stored procedure, MySQL 5 introduce stored procedure.

In SQL server you can then do SELECT * FROM database.getExamples()

If you want to re-use the 'procedure' then, yes, I would put it into a table valued function.

Otherwise you could just SELECT INTO a #temporary table inside the stored procedure.

MySQL Stored Procedure, Introduction to MySQL stored procedures. Tutorial on MySQL procedure Now select the database 'hr' and list the tables : mysql> USE hr  MySQL: Select several rows based on several keys on a given column. mysql,sql,database. If you are looking to find the records matching with both the criteria here is a way of doing it select `item_id` FROM `item_meta` where ( `meta_key` = 'category' and `meta_value` = 'Bungalow' ) or ( `meta_key` = 'location' AND `meta_value` = 'Lagos' ) group by `item_id` having count(*)=2

MySQL 8.0 Reference Manual :: 13.1.17 CREATE , To invoke a stored procedure, use the CALL statement (see Section 13.2.1, MySQL also permits stored procedures (but not stored functions) to contain SQL the EXECUTE privilege for it and the SELECT privilege for the mysql.user table​. Home >> Blog >> MySQL Stored Procedure example with parameter By Admin October 13, 2019 MySQL Stored Procedure: since every time you pass a query string to Server the code has to be compiled etc, stored procedure are already compiled and ready to run on the server.

6.1.5 Working with Stored Procedures, Using Connector/NET with Table Caching Putting database-intensive operations into stored procedures lets you define an API for your database application. For the purposes of this tutorial, you will create a simple stored procedure to see an SQL query as a parameter, it takes the name of the stored procedure to call. To execute a stored procedure, you use the CALL statement: CALL stored_procedure_name (argument_list); In this syntax, you specify the name of the stored procedure after the CALL keyword. If the stored procedure has parameters, you need to pass arguments inside parentheses following the stored procedure name.

A.4 MySQL 5.7 FAQ: Stored Procedures and Functions, In the proc table of the mysql system database. However, you should not access the tables in the system database directly. Instead, query the  Stored Procedures I have a stored procedure that inserts a user into a table but I want an output value equals to the new inserted UserID in the table but I don't know how to do it can you guys help me? I have this ALTER PROCEDURE dbo.st_Insert_User ( @Nombre varchar

Comments
  • Trying to find a solution, I'm trying the following, but obviously the return data type ('TABLE') doesn't work; <pre> CREATE PROCEDURE database.getExamples( OUT examplesTable TABLE /* <<< What datatype to use for a table? */ ) SELECT * FROM examples INTO examplesTable </pre> Am I on the right track here..?
  • Possible duplicate of Can a stored procedure/function return a table?
  • What makes it so obvious that it's not possible without temp tables? This doesn't answer the question.
  • @circuitry The grammar makes it clear that MySQL doesn't allow select * from getExmaples() .... It doesn't necessarily mean there's no other way to do it. This is a frustrating issue; in PostgreSQL, it's easy to select * from getExamples() ....
  • Thank you Dani! This means that I have to rebuild the entire table every time? That seems like lots of extra work to me. Just running CALL getExamples() obviously gives the data I need - the question is how to access it in a select statement...
  • This is SQL Server code. in mysql - you can create temporary table use it, and then drop it.
  • From performance aspect, and I'm not a pro, i think it will be almost the same as using it in a nested statement, there probably some optimization possibilities in the nested option, but unless you are dealing with very large/complicated select you won't notice it.
  • Oh, now I understand what you want to do. I guess it is possible, need to try it myself though.
  • Hi RR! I'm trying this, but obviously the data-type "TABLE" isn't valid: CREATE PROCEDURE database.getExamples( OUT examplesTable TABLE /* <<< What datatype to use for a table? */ ) SELECT * FROM examples INTO examplesTable
  • SQL Server = Microsoft SQL Server and it has different capabilities from MySQL. This means a lot of things you can do in MS SQL cannot be done in MySQL and vice verse. OP asks for MySQL.