MySQL : Use Stored Procedure in SELECT and FROM

Question

I have a question related to stored procedure. I have a table called as account with a field named agent. I have a simple SQL like this:

-- First SQL --
SELECT      account.agent
FROM        account
GROUP BY    account.agent
HAVING      account.agent > 0

If I insert this above SQL to a table called as agent_structure, I simply can JOIN this table with another table (e.g. customer) by using SQL like this:

-- Second SQL --
SELECT      agent_structure.agent,
            customer.name, 
            customer.age, 
            customer.country
FROM        agent_structure
INNER JOIN  customer ON agent_structure.agent = customer.id

The problem is, I don't want to add another table only for saving one field. So, I try to use stored procedure. So, I put the first SQL to procedure like this:

-- FIRST SQL put into Procedure --
CREATE PROCEDURE agent_structure() 
SELECT      account.agent
FROM        account
GROUP BY    account.agent
HAVING      account.agent > 0

This looks very well, since when I write 'CALL agent_structure();', the SQL output the single field that I want. However, I don't know how to use this result like in second SQL. I try this dummy way after give out parameter to the procedure, but it doesn't work:

-- Second SQL but use stored procedure --
CALL agent_structure(@a);
SELECT      @a,
            customer.name, 
            customer.age, 
            customer.country
FROM        @a
INNER JOIN  customer ON @a.agent = customer.id

The goal is like using a script to another script. I don't want to put the first script directly to the second one since my actual script is larger and have multiple layers. Anyone can help me give the solution for this?

kastriotcunaku · Accepted Answer · 2017-08-08 09:47:17Z

1

As I can see you can't include Stored Procedure inside SELECT. In this case you need to use View instead Stored Procedure.

    CREATE VIEW agent_structure AS
    SELECT      account.agent
    FROM        account
    GROUP BY    account.agent
    HAVING      account.agent > 0

    SELECT      customer.name, 
                customer.age, 
                customer.country
    FROM        agent_structure AS a
    INNER JOIN  customer AS c ON a.agent = c.id

edited Aug 8, 2017 at 9:47

answered Aug 8, 2017 at 9:23

kastriotcunaku

1,1797 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Reza Satnaz Over a year ago

It is wrong. #1064 - You have an error in your SQL syntax; Error start in 'FROM @a ... '

kastriotcunaku Over a year ago

@Tan - Try using view instead stored procedure. Here you can find more informations how to use Views

Reza Satnaz Over a year ago

This works, thanks a lot dude. I have two questions: 1) is it possible to create a view that have a view inside the script? 2) what is the differences on using procedure and view then?

kastriotcunaku Over a year ago

Great :) 1) Yes, it is possible; 2) Views acts like tables and can be combinations of different tables data; Stored procedures meant to be executed, not to act like a table;

Reza Satnaz Over a year ago

I see.. I'll learn it from some external sources... Thanks dude!

Collectives™ on Stack Overflow

MySQL : Use Stored Procedure in SELECT and FROM

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related