CODEWITHSUNDEEP
mysqlsqldatabasestored-proceduresinner-join
user1119648
user1119648

Reputation: 541

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.

enter image description here

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.

enter image description here

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.

Upvotes: 0

Views: 3975

Answers (1)

FrankPl
FrankPl

Reputation: 13315

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

Upvotes: 2

Related Questions