Select Query with Stored Procedure

Question

I want to call a procedure with some conditions, This is my code.

DELIMITER $$

USE `jijo_db`$$

DROP PROCEDURE IF EXISTS `view_all_user_details_with_limit`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `view_all_user_details_with_limit`(IN StartNo INT,IN EndNo INT, IN OrderVal VARCHAR(10),IN Cond VARCHAR(50))
BEGIN   
SELECT * FROM `tbl_user_details` WHERE  Cond  ORDER BY OrderVal LIMIT StartNo,EndNo;
END$$

DELIMITER ;

procedure call - CALL view_all_user_details_with_limit(0,10,'',"NAME LIKE '%a%'");

but I dont get any result. why ????

Nick · Accepted Answer · 2018-08-28 06:10:20Z

2

If you want to have variable WHERE, LIMIT, and ORDER BY conditions, you will need to create a prepared statement in your stored procedure.

Try something like this:

DELIMITER $$

USE `jijo_db`$$

DROP PROCEDURE IF EXISTS `view_all_user_details_with_limit`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `view_all_user_details_with_limit`(IN StartNo INT,IN EndNo INT, IN OrderVal VARCHAR(10),IN Cond VARCHAR(50))
BEGIN
   SET @q = CONCAT('SELECT * FROM `tbl_user_details` WHERE ', Cond);
   IF OrderVal != '' THEN
      SET @q = CONCAT(@q, ' ORDER BY ', OrderVal);
   END IF;
   SET @q = CONCAT(@q, ' LIMIT ', StartNo, ', ', EndNo - StartNo + 1);
   PREPARE stmt FROM @q;
   EXECUTE stmt;
END$$

DELIMITER ;

answered Aug 28, 2018 at 6:10

Nick

147k23 gold badges67 silver badges106 bronze badges

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

4 Comments

JIJOMON K.A Over a year ago

:- Its working, but how to pass multiple conditions like CALL view_all_user_details_with_limit(0,10,'',"Name LIKE '%i%' or Gender='Male'");

Nick Over a year ago

It should work fine as it is. Are you seeing an error?

JIJOMON K.A Over a year ago

Query: call view_all_user_details_with_limit(0,10,'',"Name LIKE '%i%' or Gender='Male'") Error Code: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''Mal LIMIT 0, 11' at line 1

Nick Over a year ago

That looks like your condition got truncated (it lost the final e' from the end). Do you have a long enough parameter (I have used VARCHAR(50) and in my test version of this which just echos the query I get SELECT * FROM `tbl_user_details` WHERE Name LIKE '%i%' or Gender='Male' LIMIT 0, 11

Steven Moseley · Accepted Answer · 2018-08-28 04:48:00Z

1

Your problem seems to be happening in WHERE Cond.

A MySQL Stored Procedure won't interpret that string as an expression, but will instead attempt to cast it to a Boolean.

You can see by running SELECT CAST("NAME LIKE '%a%'" AS UNSIGNED); that the string will be interpreted as False, and thereby you won't get any results.

I suggest instead that you accept in your Cond variable a string such as '%a%' and then update your query to:

SELECT * 
FROM `tbl_user_details` 
WHERE `NAME` LIKE Cond
ORDER BY OrderVal
LIMIT StartNo, EndNo;

answered Aug 28, 2018 at 4:48

Steven Moseley

16.4k4 gold badges43 silver badges50 bronze badges

2 Comments

Steven Moseley Over a year ago

Another note - EndNo in your variables is a little confusing, as MySQL accepts in the LIMIT clause a Start Number and Slice Length. So 100,110 will return results 100-209. If you want a slice of 10 objects from 100-109, you would specify LIMIT 100, 10

JIJOMON K.A Over a year ago

but I have more than one search criterias. I want to pass the conditions dyanmically.

Collectives™ on Stack Overflow

Select Query with Stored Procedure

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related