MySQL procedure in Select?

Question

I have a procedure that works as follows:

mysql> call Ticket_FiscalTotals(100307); +---------+--------+----------+------------+------------+ | Service | Items | SalesTax | eTaxAmount | GrandTotal | +---------+--------+----------+------------+------------+ | 75.00 | 325.00 | 25.19 | 8.00 | 433.19 | +---------+--------+----------+------------+------------+ 1 row in set (0.08 sec) 

I would like to call this procedure from within select, for example:

 SELECT Ticket.TicketID as `Ticket`, Ticket.DtCheckOut as `Checkout Date / Time`, CONCAT(Customer.FirstName, ' ', Customer.LastName) as `Full Name`, Customer.PrimaryPhone as `Phone`, (CALL Ticket_FiscalTotals(Ticket.TicketID)).Service as `Service` FROM Ticket INNER JOIN Customer ON Ticket.CustomerID = Customer.CustomerID ORDER BY Ticket.SiteHomeLocation, Ticket.TicketID 

However, I know this is painfully wrong. Can someone please point me in the right direction? I need access to all columns from the procedure that will be (attached?) In the final Select. The SQL code inside this procedure is quite painful, so the reason for this is first!

Answer 1

The Ticket_FiscalTotals procedure returns a data set with some fields, but you only need one of them - Service . Rewrite your procedure on the stored function - Get_Ticket_FiscalTotals_Service .

Another way is to create and populate a temporary table in the procedure and add this temporary value to the query, for example:

 DELIMITER $$ CREATE PROCEDURE Ticket_FiscalTotals() BEGIN DROP TEMPORARY TABLE IF EXISTS temp1; CREATE TEMPORARY TABLE temp1( Service FLOAT(10.2), Items FLOAT(10.2), SalesTax FLOAT(10.2), eTaxAmount FLOAT(10.2), GrandTotal FLOAT(10.2) ); INSERT INTO temp1 VALUES (75.0, 325.0, 25.19, 8.0, 433.19); END $$ DELIMITER ; -- Usage CALL Ticket_FiscalTotals(); SELECT t.*, tmp.service FROM Ticket t, temp1 tmp; 

Answer 2

You cannot directly join a stored procedure. You can join the temporary table that this stored procedure populates:

• create a temporary table
• run an SP that populates the data in your temporary table,
• join the temp table in your query,
• temp table temp.

Of course, this is not one linear solution.

In another way (worse, in my opinion), I think it is to have as many UDFs as there are columns in the SP result set, it may look like pluggable code:

 SELECT Ticket.TicketID as `Ticket`, Ticket.DtCheckOut as `Checkout Date / Time`, CONCAT(Customer.FirstName, ' ', Customer.LastName) as `Full Name`, Customer.PrimaryPhone as `Phone`, Ticket_FiscalTotals_Service(Ticket.TicketID) as `Service`, Ticket_FiscalTotals_Items(Ticket.TicketID) as `Items`, Ticket_FiscalTotals_SalesTax(Ticket.TicketID) as `SalesTax`, Ticket_FiscalTotals_eTaxAmount(Ticket.TicketID) as `eTaxAmount`, Ticket_FiscalTotals_GrandTotal(Ticket.TicketID) as `GrandTotal` FROM Ticket INNER JOIN Customer ON Ticket.CustomerID = Customer.CustomerID ORDER BY Ticket.SiteHomeLocation, Ticket.TicketID