CODEWITHSUNDEEP
mysqlstored-procedures
Pratik Thube
Pratik Thube

Reputation: 134

MySql procedure unable to filter query result properly for string input

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!

Upvotes: 0

Views: 69

Answers (1)

Jacques Amar
Jacques Amar

Reputation: 1833

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.

Upvotes: 1

Related Questions