Problems with mysql stored procedures

  • 2020-05-07 20:34:13
  • OfStack

1 started with phpMyAdmin, then 1 heap of errors, then begin was removed,end can execute normally, but to execute stored procedures, not phpMyAdmn, but mysql command line text box.
Next, more difficult problems, in the stored procedure to add preprocessing statements, not to mention, in mysql command line text box to execute the same, the following running records, for your reference, whether there is a master to help.

mysql> CREATE PROCEDURE catalog_get_products_in_category( 
-> IN inCategoryId INT, IN inShortProductDescriptionLength INT, 
-> IN inProductsPerPage INT, IN inStartItem INT) 
-> begin 
-> SELECT p.product_id, p.name,IF(LENGTH(p.description) <= inShortProductD 
escriptionLength, p.description, 
-> CONCAT(LEFT(p.description, inShortProductDescriptionLength),'...')) AS 
description, p.price, p.discounted_price, p.thumbnail 
-> FROM product p INNER JOIN product_category pc ON p.product_id = pc.pro 
duct_id 
-> WHERE pc.category_id = inCategoryId 
-> ORDER BY p.display DESC 
-> LIMIT inStartItem;inProductsPerPage; 
-> end$$ 
ERROR 1064 (42000): 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 'inSta 
rtItem;inProductsPerPage; 
end' at line 10 

// original stored procedure statement
EATE PROCEDURE catalog_get_products_in_category(
IN inCategoryId INT, IN inShortProductDescriptionLength INT,
IN inProductsPerPage INT, IN inStartItem INT)
BEGIN
PREPARE statement FROM
"SELECT p.product_id, p.name,IF(LENGTH(p.description) < = ?, p.description,
CONCAT(LEFT(p.description, ?),'...')) AS description, p.price, p.discounted_price, p.thumbnail
FROM product p INNER JOIN product_category pc ON p.product_id = pc.product_id
WHERE pc.category_id = ?
ORDER BY p.display DESC
LIMIT ?, ?";
SET @p1 = inShortProductDescriptionLength;
SET @p2 = inShortProductDescriptionLength;
SET @p3 = inCategoryId;
SET @p4 = inStartItem;
SET @p5 = inProductsPerPage;
EXECUTE statement USING @p1, @p2, @p3, @p4, @p5;
END$$

mysql > delimiter $$
mysql > CREATE PROCEDURE catalog_get_products_in_category(
- > IN inCategoryId INT, IN inShortProductDescriptionLength INT,
- > IN inProductsPerPage INT, IN inStartItem INT)
- > BEGIN
- > PREPARE statement FROM
- > "SELECT p.product_id, p.name,IF(LENGTH(p.description) < = ?, p.descript
ion,
" > CONCAT(LEFT(p.description, ?),'...')) AS description, p.price, p.disco
unted_price, p.thumbnail
" > FROM product p INNER JOIN product_category pc ON p.product_id = pc.pro
duct_id
" > WHERE pc.category_id = ?
" > ORDER BY p.display DESC
" > LIMIT ?, ?";
- > SET @p1 = inShortProductDescriptionLength;
- > SET @p2 = inShortProductDescriptionLength;
- > SET @p3 = inCategoryId;
- > SET @p4 = inStartItem;
- > SET @p5 = inProductsPerPage;
- > EXECUTE statement USING @p1, @p2, @p3, @p4, @p5;
- > END$$
ERROR 1314 (0A000): PREPARE is not allowed in stored procedures

There are two stored procedures, one without a preprocessing statement, one with a preprocessing statement,
After that, I sent an email to the author, but did not reply. I also sent an email to mysql, but did not reply. Now I can only turn to you.

Related articles: