MySQL Procedure within a Select?

Question

I have a procedure that works like this:

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 a select, like so:

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 that this is painfully wrong. Can someone please point me in the proper direction? I will need access to all of the columns from the procedure to be (joined?) in the final Select. The SQL code within that procedure is rather painful, hence the reason for it in the first place!

Solution 1

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

Another way is to create and fill temporary table in the procedure, and add this temporary to a query, e.g.:

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;

Solution 2

You can't join directly to stored procedure. You can join to temporary table that this stored procedure fills:

1. create temporary table,
2. execute SP that fills data in your temp table,
3. join to temp table in your query,
4. drop temp table.

Of course it is not one line solution.

The other way (worse in my opinion) I think of is to have as many UDF as columns in SP result set, this might look like fallowing 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

Comments

[Fuginator]

Thank you for your input. I stated above that I will need access to all of the columns from the procedure to be (joined?) in the final Select, meaning that I do need Service, Items, SalesTax, eTaxAmount, and GrandTotal in the final table. Will the temp. table have to be generated several hundred times if there are several hundred results in the outer Select? (in the Ticket table) - and if so, souldent the procedure that creates the temp. table also need to be called within the Select? (to re-generate the temp. table) Again, thank you for your input - this is turning into quite the pain...

[Devart]

Temporary table will be generated once - when you call procedure After, you will be able to use this tenp. table many times (note, temp. table is accessible only to the current session).

[Fuginator]

Thanks for the input, I wound up using a nested view solution instead, as the performance turned out being much better on my setup. Thanks again for your input.