MySql procedure unable to filter query result properly for string input

Question

I have following procured in my MySql database, which works fine for first three queries. But userId filter is not working, It should return entries with the matched UserId, but it is returning me all the records in that table. This is happening for both queries(Query of details and detailstwo table).

DELIMITER $$

DROP PROCEDURE IF EXISTS `sale`.`detailsProcedure` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `detailsProcedure`(IN userId VARCHAR(100))
BEGIN
  SELECT * FROM products WHERE ProductType=1 AND Price<=400;
  SELECT * FROM products WHERE ProductType=1 AND Price<=700;
  SELECT * FROM products WHERE ProductType=2;
  SELECT * FROM products WHERE ProductType=3;
  SELECT * FROM details WHERE UserId=userId;
  SELECT * FROM detailsTwo WHERE UserId=userId;
END $$

DELIMITER

;

Seems like UserId is not getting compared or no filtration effect. Is there anything wrong with syntax. I looked into several post but didn't find particular solution which will help me,tried several way from stack overflow it self but problem persist.looking for help. Note:- I am calling this procedure in my node.js restapi

Thanks In Advance!

Have you tried UserId=@userId Also, maybe rename your input variable different, like v_userId to make sure it's not confused — Jacques Amar
– Jacques Amar, Commented Sep 27, 2017 at 20:28

Jacques Amar · Accepted Answer · 2017-09-28 17:33:54Z

1

Try this one:

DELIMITER $$

DROP PROCEDURE IF EXISTS `sale`.`detailsProcedure` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `detailsProcedure`(IN v_userId VARCHAR(100))
BEGIN
  SELECT * FROM products WHERE ProductType=1 AND Price<=400;
  SELECT * FROM products WHERE ProductType=1 AND Price<=700;
  SELECT * FROM products WHERE ProductType=2;
  SELECT * FROM products WHERE ProductType=3;
  SELECT * FROM details WHERE UserId=v_userId;
  SELECT * FROM detailsTwo WHERE UserId=v_userId;
END $$

DELIMITER ;

Seems there was confusion in all the userId variations.

edited Sep 28, 2017 at 17:33

answered Sep 27, 2017 at 20:32

Jacques Amar

1,8231 gold badge12 silver badges12 bronze badges

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

1 Comment

Pratik Thube Over a year ago

strange, userId to v_userId worked ,but "@v_userId" didn't. Thanks @Jacques Amar . you can edit ans if you want to(by removing "@" sign).

Collectives™ on Stack Overflow

MySql procedure unable to filter query result properly for string input

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related