0

DELIMITER // -- Create a stored procedure with params CREATE PROCEDURE GetProductsList(MainSubcategory VARCHAR(500), SubcategoryChildName VARCHAR(500), OffsetVal INT, NumberOfRecordPerPage INT, orderLowToHigh Boolean) READS SQL DATA BEGIN

SET @MainSubcategory = MainSubcategory;
SET @SubcategoryChildName = SubcategoryChildName;
IF orderLowToHigh = true THEN
    SET @SortOrder = 'ASC';
ELSE 
     SET @SortOrder = 'DESC';
END IF;
SET @OffsetVal = OffsetVal;
SET @NumberOfRecordPerPage = NumberOfRecordPerPage;

PREPARE mainQuery FROM "SELECT title, product_category_tree, id, images, price, old_price
FROM PRODUCTS
WHERE MATCH(product_category_tree) AGAINST (MainSubcategory IN NATURAL LANGUAGE MODE) AND 
product_category_tree LIKE CONCAT('%',?,' >> ',?,'%')
ORDER BY price ?
LIMIT ?, ?;";

EXECUTE mainQuery USING @MainSubcategory, @SubcategoryChildName, @SortOrder, @OffsetVal, @NumberOfRecordPerPage;
DEALLOCATE PREPARE mainQuery;

END// -- Change the delimiter again DELIMITER ;

when I called this procedure I get an error: Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? LIMIT ?, ?' at line 5 0.0026 sec

It looks like I cannot have variables in limit clause. Is there a workaround in mysql?

MoBal
  • 13
  • 3
  • Does this answer your question? [How to apply bindValue method in LIMIT clause?](https://stackoverflow.com/questions/2269840/how-to-apply-bindvalue-method-in-limit-clause) – Tangentially Perpendicular Dec 03 '21 at 01:27
  • As you are guessing, the engine allows parameters at specific locations of the SQL query only. The typical solution is to use Dynamic SQL and assemble that last section of the query at runtime, by concatenating strings. – The Impaler Dec 03 '21 at 01:29
  • @TangentiallyPerpendicular I am not using PHP in my SQL queries. Sorry, but I am new to SQL, is the only way to have variable in limit clause of stored procedure to use the bind method from PHP MySQL? – MoBal Dec 03 '21 at 01:34
  • Also according to official docs, https://dev.mysql.com/doc/refman/8.0/en/select.html, it has an example with variables in limit clause The following statements return the second to sixth rows from the tbl table: SET @skip=1; SET @numrows=5; PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?'; EXECUTE STMT USING @skip, @numrows; However, I am not sure why mine is giving an error, I think it is taking the variable as strings and not int values – MoBal Dec 03 '21 at 01:35
  • Unless I"m missing it, you're executing with 4 variables but only 3 placeholders. Shouldn't your (MainSubcategory IN NATURAL LANGUAGE MODE) actually be (? IN NATURAL LANGUAGE MODE) – Lance Dec 03 '21 at 01:40
  • @Lance I could not paste the full query, but now I updated the full query – MoBal Dec 03 '21 at 01:49
  • @MoBal I'm not aware of a limitation on having the LIMIT clause manipulated via prepared statement parameter placeholders. – Lance Dec 03 '21 at 01:51
  • @Lance I can just not use PREPARE statement and directly use my variables in stored procedure. That works. How important do u think it is to use Prepare statements? – MoBal Dec 03 '21 at 02:08

0 Answers0