MySQL query with Inner Join returns all records in Stored procedure
I have a database where I use an inner join to return a more useful set of
information. When I run:
SELECT
`invoices`.`property`,`invoices`.`invoice_number`,`invoices`.`date`,`customers`.`company_name`,`invoices`.`total`
FROM `invoices`
INNER JOIN `customers` ON `invoices`.`customer` = `customers`.`customer_id`
WHERE `invoices`.`property` = 'CGC' ORDER BY `invoices`.`date` DESC;
On its own, I get back exactly what I expect.
But when I create a stored procedure with the same query, accepting a
single argument, a la:
DELIMITER $$
USE `techrentals`$$
DROP PROCEDURE IF EXISTS `getInvoiceList`$$
CREATE DEFINER=`root`@`%` PROCEDURE `getInvoiceList`(IN prop_id INT)
BEGIN
SELECT
`invoices`.`property`,`invoices`.`invoice_number`,`invoices`.`date`,`customers`.`company_name`,`invoices`.`total`
FROM `invoices`
INNER JOIN `customers` ON `invoices`.`customer` =
`customers`.`customer_id`
WHERE `invoices`.`property` = prop_id ORDER BY `invoices`.`date` DESC;
END$$
DELIMITER ;
And then I execure that stored procedure:
CALL getInvoiceList('CGC');
It returns every invoice in the set.
Any Idea why it doesn't return exactly the same set? I'm no MySQL expert,
but it seems to me that those should be functionally identical.
Thanks in advance.
No comments:
Post a Comment