使用内连接的MySQL查询返回存储过程中的所有记录。

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:

但是，当我用相同的查询创建一个存储过程时，接受一个参数，一个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.

知道为什么它不能返回完全相同的集合吗?我不是MySQL专家，但在我看来，这些应该是功能相同的。

Thanks in advance.

提前谢谢。

1 个解决方案

#1

You declare the parameter type as int, but pass a string in the call.

您将参数类型声明为int，但在调用中传递一个字符串。