CODEWITHSUNDEEP
mysqlsqlstored-proceduresuser-defined-functions
Fuginator
Fuginator

Reputation: 229

MySQL Procedure within a Select?

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!

Upvotes: 6

Views: 28046

Answers (2)

Devart
Devart

Reputation: 121912

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;

Upvotes: 8

Michał Powaga
Michał Powaga

Reputation: 23183

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

Upvotes: 7

Related Questions