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
//原本的存儲過程語句 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
