mysql function parameter not work in where condition of select statement

Question

DELIMITER $$
     CREATE DEFINER=`axistms`@`localhost` FUNCTION `CheckDoc`(`orderId` INT) RETURNS int(11)
DETERMINISTIC
BEGIN
     DECLARE lvl int;
     SELECT count(`id`) INTO lvl FROM `com_carrier_portal_upload_documents` WHERE `orderID`= orderId;
     RETURN lvl;
END$$
DELIMITER ;

any order id pass through orderId parameter its doesn't effect on where condition. Always return count of all records.How to fix this?

Devart · Accepted Answer · 2018-09-04 09:58:19Z

1

I'd suggest you to rename stored procedure argument, do something like this -

CREATE DEFINER = `axistms`@`localhost` FUNCTION `CheckDoc` (orderIdParam int)
RETURNS int(11)
DETERMINISTIC
BEGIN
  DECLARE lvl int;
  SELECT
    COUNT(`id`) INTO lvl
  FROM `com_carrier_portal_upload_documents`
  WHERE `orderID` = orderIdParam;
  RETURN lvl;
END

...because WHERE orderID= orderId can be equal to WHERE true.

answered Sep 4, 2018 at 9:58

Devart

120k23 gold badges172 silver badges189 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql function parameter not work in where condition of select statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related