Start with the phpMyAdmin to execute, and then a bunch of errors, then remove the begin,end can normally execute, but to execute the stored procedure, in phpmyadmn, and in the MySQL command line text box can.
The next encounter more difficult problems, in the stored procedure to add preprocessing statements, even worse, in the MySQL command line text box to execute the same, the following running records, for you to refer to, whether there is a master to help.
Copy Code code as follows:
Mysql> CREATE PROCEDURE catalog_get_products_in_category (
-> in Incategoryid int, 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 a 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 ' in line 10
The 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, 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 isn't allowed in stored procedures
There are two stored procedures, one without preprocessing statements, one with preprocessing statements,
After, sent an email to the author, no reply, and gave the MySQL official email, also no reply. Now I can only turn to you.
