CODEWITHSUNDEEP
mysqlstored-proceduresphpmyadmin
deepu sankar
deepu sankar

Reputation: 4455

MySQL stored procedure syntax error for cursor select

  DROP PROCEDURE CREATE_DUPLICATE_PRODUCT;
  DELIMITER $$
  CREATE PROCEDURE `CREATE_DUPLICATE_PRODUCT`(IN `old_course_id` INT(25), IN `new_course_id` 
  INT(25))
 BEGIN

DECLARE db_cursor CURSOR FOR SELECT lic.org_id, lic.course_id, lic.license_duration_typeid, lic.insert_date, lic.insert_by, lic.quantity FROM  cdp_organization_licenses as lic JOIN cdp_organization_license_settings as sett ON sett.org_license_id = lic.id JOIN cdp_organization_purchases as pur ON pur.org_id = lic.org_id AND pur.course_id = lic.course_id JOIN cdp_organizations as org ON org.org_id = lic.org_id WHERE  lic.status_id = 1 AND org.status_id = 1;
DECLARE @org_id INT;
DECLARE @course_id INT;
DECLARE @license_typeid INT;
DECLARE @insert_date INT;
DECLARE @insert_by INT;
DECLARE @quantity INT;
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @org_id, @course_id, 
@license_duration_typeid, @insert_date, @insert_by, @quantity;
WHILE @@FETCH_STATUS = 0  
BEGIN  

--Do stuff with scalar values

FETCH NEXT FROM db_cursor INTO @org_id, @course_id,
@license_duration_typeid, @insert_date, @insert_by, @quantity;
    INSERT INTO cdp_organization_licenses_test
        SET
            org_id = @org_id,
            course_id = @course_id,
            license_duration_typeid = @license_duration_typeid,
            insert_date = @insert_date,
            insert_by = @insert_by,
            quantity = @quantity;
END;
CLOSE db_cursor;
DEALLOCATE db_cursor;
END$$
DELIMITER ;

I am getting this error:

   #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 '@org_id INT;
DECLARE @course_id INT;
DECLARE @license_typeid INT; ' at line 5

What can I do to resolve this?

Upvotes: 0

Views: 72

Answers (2)

O. Jones
O. Jones

Reputation: 108651

Don't DECLARE variables with @ at the beginning of their names. Those are session variables, don't need to be declared, and have session-wide scope,

Upvotes: 0

nbk
nbk

Reputation: 49375

You are mixing sql serv and mysql , that can not work

  DROP PROCEDURE CREATE_DUPLICATE_PRODUCT;
  DELIMITER $$
  CREATE PROCEDURE `CREATE_DUPLICATE_PRODUCT`(IN `old_course_id` INT(25), IN `new_course_id` 
  INT(25))
 BEGIN


DECLARE _org_id INT;
DECLARE _course_id INT;
DECLARE _license_typeid INT;
DECLARE _insert_date INT;
DECLARE _insert_by INT;
DECLARE _quantity INT;
DECLARE finished INTEGER DEFAULT 0;
DECLARE db_cursor CURSOR FOR 
    SELECT lic.org_id, lic.course_id, lic.license_duration_typeid, lic.insert_date, lic.insert_by, lic.quantity 
    FROM  cdp_organization_licenses as lic 
        JOIN cdp_organization_license_settings as sett ON sett.org_license_id = lic.id 
        JOIN cdp_organization_purchases as pur ON pur.org_id = lic.org_id AND pur.course_id = lic.course_id 
        JOIN cdp_organizations as org ON org.org_id = lic.org_id 
    WHERE  lic.status_id = 1 AND org.status_id = 1;
        DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET finished = 1;
        
        OPEN db_cursor;

        getinfo: LOOP
            BEGIN  

                FETCH NEXT FROM db_cursor INTO _org_id, _course_id,
                        _license_duration_typeid, _insert_date, _insert_by, _quantity;
                IF finished = 1 THEN 
                    LEAVE getEmail;
                END IF;
                INSERT INTO cdp_organization_licenses_test
                SET
                org_id = _org_id,
                course_id = _course_id,
                license_duration_typeid = _license_duration_typeid,
                insert_date = _insert_date,
                insert_by = _insert_by,
                quantity = _quantity;
            END;
    END LOOP getinfo;
CLOSE db_cursor;

END$$
DELIMITER ;

But t´you can simply do a

INSERT INTO cdp_organization_licenses_test     
SELECT lic.org_id, lic.course_id, lic.license_duration_typeid, lic.insert_date, lic.insert_by, lic.quantity 
    FROM  cdp_organization_licenses as lic 
        JOIN cdp_organization_license_settings as sett ON sett.org_license_id = lic.id 
        JOIN cdp_organization_purchases as pur ON pur.org_id = lic.org_id AND pur.course_id = lic.course_id 
        JOIN cdp_organizations as org ON org.org_id = lic.org_id 
    WHERE  lic.status_id = 1 AND org.status_id = 1;

That makes all with out the loop

Upvotes: 1

Related Questions