MySQL Store Procedure Error Code: 1064

Question

I am trying to create stored procedure and getting error:

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 'DECLARE @LoopCounter INT DEFAULT 0; DECLARE @MaxId INT DEFAULT 0; DECLARE ' at line 21

DELIMITER $$
USE dollar$$
DROP PROCEDURE IF EXISTS sp_get_products_google_feed$$
CREATE DEFINER=root@localhost PROCEDURE sp_get_products_google_feed()
BEGIN
        DROP TABLE IF EXISTS tmp_Product_List;
        CREATE TEMPORARY TABLE tmp_Product_List(
            SELECT DISTINCT p.products_id AS PID, p.products_model AS ID, pd.products_name AS Title, pd.products_description AS Description, 
            '' AS Google_product_category, '' AS product_type, p.products_model AS link, p.products_image AS Image_link,
            'new' AS Condition1, 'in stock' AS Availability, p.products_price AS Price, '' AS Sale_Price, '' AS Sale_price_effective_date,
            p.products_upc AS GTin, p.manufacturers_id, '' AS MPN, '' AS Item_group_id, '' AS Gender, '' AS Age_group, '' AS Color, '' AS Size,
            'Free' AS Shipping, '' AS Shipping_Weight 
            FROM 
            zc_products_to_categories pc, zc_products p, zc_products_description pd  WHERE 
            pc.categories_id IN 
            (SELECT DISTINCT mg.sub_category_id AS id FROM tbl_map_google_category_master mg WHERE mg.category_id = 1 
            UNION
            SELECT DISTINCT mg.sub_sub_category_id  AS id FROM tbl_map_google_category_master mg WHERE mg.category_id = 1
            ORDER BY id) AND
            p.products_id = pc.products_id AND
            p.products_id = pd.products_id  AND
            p.products_status = 1 ORDER BY PID);

            DECLARE @LoopCounter INT DEFAULT 0;
            DECLARE @MaxId INT DEFAULT 0;
            DECLARE @GoogleCategoryid INT DEFAULT 0;

            SELECT @LoopCounter = MIN(PID), @MaxId = MAX(PID) FROM tmp_Product_List;

            WHILE (@LoopCounter IS NOT NULL AND @LoopCounter <= @MaxId)
            BEGIN
                SELECT @GoogleCategoryid = google_category_id FROM tbl_map_google_category_master 
                WHERE 
                category_id = (SELECT MAX(categories_id) FROM zc_products_to_categories WHERE products_id = @LoopCounter) OR 
                sub_category_id = (SELECT MAX(categories_id) FROM zc_products_to_categories WHERE products_id = @LoopCounter) OR 
                sub_sub_category_id = (SELECT MAX(categories_id) FROM zc_products_to_categories WHERE products_id = @LoopCounter) LIMIT 0,1;

                UPDATE tmp_Product_List SET Google_product_category = @GoogleCategoryid WHERE products_id = @LoopCounter;

                SET @LoopCounter  = @LoopCounter  + 1               
                IF(@@ROWCOUNT = 0 )
                BEGIN
                    SET @LoopCounter  = @LoopCounter  + 1 
                    CONTINUE
                END
            END

            SELECT * FROM tmp_Product_List;
    END$$
DELIMITER ;

But if I remove below code from script, it run successfully. Trying to find error in full script but no luck.

            DECLARE @LoopCounter INT DEFAULT 0;
            DECLARE @MaxId INT DEFAULT 0;
            DECLARE @GoogleCategoryid INT DEFAULT 0;

            SELECT @LoopCounter = MIN(PID), @MaxId = MAX(PID) FROM tmp_Product_List;

            WHILE (@LoopCounter IS NOT NULL AND @LoopCounter <= @MaxId)
            BEGIN
                SELECT @GoogleCategoryid = google_category_id FROM tbl_map_google_category_master 
                WHERE 
                category_id = (SELECT MAX(categories_id) FROM zc_products_to_categories WHERE products_id = @LoopCounter) OR 
                sub_category_id = (SELECT MAX(categories_id) FROM zc_products_to_categories WHERE products_id = @LoopCounter) OR 
                sub_sub_category_id = (SELECT MAX(categories_id) FROM zc_products_to_categories WHERE products_id = @LoopCounter) LIMIT 0,1;

                UPDATE tmp_Product_List SET Google_product_category = @GoogleCategoryid WHERE products_id = @LoopCounter;

                SET @LoopCounter  = @LoopCounter  + 1               
                IF(@@ROWCOUNT = 0 )
                BEGIN
                    SET @LoopCounter  = @LoopCounter  + 1 
                    CONTINUE
                END
            END

Move your DECLARE statements to just below the BEGIN. I believe MySQL is funny about having the variables declared first. — flip, Jan 12 '18 at 13:42

score 0 · Answer 1 · answered Jan 12 '18 at 15:19

You had a few issues that I've worked through.

The first is the declaring of the variables with an @ sign before them, I've removed those.

The second was in the WHILE ... BEGIN ... END. The syntax is WHILE ... DO ... END WHILE.

The third is a missing semi-colon just before the IF(@@ROWCOUNT).

The last one that I haven't fixed below is the use of @@ROWCOUNT. @@ROWCOUNT isn't a variable in MySQL. You can find alternatives here

DELIMITER $$
USE dollar$$
DROP PROCEDURE IF EXISTS sp_get_products_google_feed$$
CREATE DEFINER=root@localhost PROCEDURE sp_get_products_google_feed()
    BEGIN
        DECLARE LoopCounter INT DEFAULT 0;
        DECLARE MaxId INT DEFAULT 0;
        DECLARE GoogleCategoryid INT DEFAULT 0;
    DROP TABLE IF EXISTS tmp_Product_List;
    CREATE TEMPORARY TABLE tmp_Product_List(
        SELECT DISTINCT p.products_id AS PID, p.products_model AS ID, pd.products_name AS Title, pd.products_description AS Description, 
        '' AS Google_product_category, '' AS product_type, p.products_model AS link, p.products_image AS Image_link,
        'new' AS Condition1, 'in stock' AS Availability, p.products_price AS Price, '' AS Sale_Price, '' AS Sale_price_effective_date,
        p.products_upc AS GTin, p.manufacturers_id, '' AS MPN, '' AS Item_group_id, '' AS Gender, '' AS Age_group, '' AS Color, '' AS Size,
        'Free' AS Shipping, '' AS Shipping_Weight 
        FROM 
        zc_products_to_categories pc, zc_products p, zc_products_description pd  WHERE 
        pc.categories_id IN 
        (SELECT DISTINCT mg.sub_category_id AS id FROM tbl_map_google_category_master mg WHERE mg.category_id = 1 
        UNION
        SELECT DISTINCT mg.sub_sub_category_id  AS id FROM tbl_map_google_category_master mg WHERE mg.category_id = 1
        ORDER BY id) AND
        p.products_id = pc.products_id AND
        p.products_id = pd.products_id  AND
        p.products_status = 1 ORDER BY PID);



        SELECT LoopCounter = MIN(PID), MaxId = MAX(PID) FROM tmp_Product_List;

        WHILE (LoopCounter IS NOT NULL AND LoopCounter <= MaxId)
        DO
            SELECT GoogleCategoryid = google_category_id FROM tbl_map_google_category_master 
            WHERE 
            category_id = (SELECT MAX(categories_id) FROM zc_products_to_categories WHERE products_id = LoopCounter) OR 
            sub_category_id = (SELECT MAX(categories_id) FROM zc_products_to_categories WHERE products_id = LoopCounter) OR 
            sub_sub_category_id = (SELECT MAX(categories_id) FROM zc_products_to_categories WHERE products_id = LoopCounter) LIMIT 0,1;

            UPDATE tmp_Product_List SET Google_product_category = GoogleCategoryid WHERE products_id = LoopCounter;

            SET LoopCounter  = LoopCounter  + 1;               
            IF(@@ROWCOUNT = 0 )
            BEGIN
                SET LoopCounter  = LoopCounter  + 1 
                CONTINUE
            END
        END WHILE

        SELECT * FROM tmp_Product_List;
END$$
DELIMITER ;

I tried but no luck. Even I removed IF(@@ROWCOUNT) conditon from script, still same error as I posted above. Seems, I have to handle this part using PHP Script now as that would be easy for me instead of finding error in this. Thanks Anyway :) — miketyon, Jan 13 '18 at 07:44

MySQL Store Procedure Error Code: 1064

1 Answers1