MySQL Stored Procedure Error

I am having an error when trying to create a stored procedure it throws some syntax error and i am unable to figure out what the problem is :(
the code of stored procedure is in code section, and  my MySQL version is 5.1.36
Please guide me what is the problem with this code :(
Create Procedure RefineData()
Begin
Create Temporary Table Temp As Select Distinct BankName FROM `BankList`;
DECLARE R_N_F INT DEFAULT 0;
DECLARE BankNames CURSOR FOR SELECT * FROM Temp;
DECLARE Bank_Name VARCHAR(100) DEFAULT "";
DECLARE CONTINUE HANDLER FOR NOT FOUND SET R_N_F = 1;
OPEN BankNames;
     allBanks: LOOP
               FETCH BankNames INTO Bank_Name;
               IF R_N_F THEN
                  LEAVE allStudents;
               END IF;
               
               DECLARE Bank_URL VARCHAR(100) DEFAULT NULL;
               DECLARE Bank_Login_URL VARCHAR(100) DEFAULT NULL;
               Bank_URL = Select BankURL From (Select Distinct BankURL, Length(BankURL) As Rank From BankList Where BankName = Bank_Name Order By Rank ASC Limit 1) As a;
               Bank_Login_URL = Select BankURL From (Select Distinct BankURL, Length(BankURL) As Rank From BankList Where BankName = Bank_Name Order By Rank DESC Limit 1) As b;
               Insert Into `RefinedBankList` Select Bank_Name, Bank_URL, Bank_Login_URL;
     END LOOP allBanks;
CLOSE BankNames;
Drop Table Temp;
End

Open in new window

LVL 2
waqarkhan88Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SoLostCommented:
First question... what exactly is the syntax error that it throws up?
0
SoLostCommented:
I don't know much about stored procedures but I had a bit of a play by trying to create your stored procedure line by line and dealing with the error messages 1 at a time.

In order to get it to compile I had to re-order your DECLARE statements and change your SELECT statements from :

Bank_URL = Select BankURL From

to

SELECT BankURL INTO Bank_URL FROM

Try the below code.  I think it will work.
DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`RefineData` $$
CREATE PROCEDURE `test`.`RefineData` ()


BEGIN

DECLARE R_N_F INT DEFAULT 0;
DECLARE Bank_Name VARCHAR(100) DEFAULT "";
DECLARE Bank_URL VARCHAR(100) DEFAULT NULL;
DECLARE Bank_Login_URL VARCHAR(100) DEFAULT NULL;
DECLARE BankNames CURSOR FOR SELECT * FROM Temp;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET R_N_F = 1;

CREATE TEMPORARY TABLE Temp AS SELECT DISTINCT BankName FROM `BankList`;

OPEN BankNames;
     allBanks: LOOP
               FETCH BankNames INTO Bank_Name;
               IF R_N_F THEN
                  LEAVE allBanks;
               END IF;

               SELECT BankURL INTO Bank_URL FROM (SELECT DISTINCT BankURL, Length(BankURL) AS Rank FROM BankList WHERE BankName = Bank_Name ORDER BY Rank ASC LIMIT 1) AS a;
               SELECT BankURL INTO Bank_Login_URL FROM (SELECT DISTINCT BankURL, Length(BankURL) AS Rank FROM BankList WHERE BankName = Bank_Name ORDER BY Rank DESC LIMIT 1) As b;
               INSERT INTO `RefinedBankList` SELECT Bank_Name, Bank_URL, Bank_Login_URL;
     END LOOP allBanks;
CLOSE BankNames;

DROP TABLE Temp;

END $$

DELIMITER ;

Open in new window

0
waqarkhan88Author Commented:
The syntax error is :
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 'DECLARE R_N_F INTEGER DEFAULT 0;
DECLARE BankNames CURSOR FOR SELECT * FROM Tem' at line 4

The more refined code is below having still same problem :(

DELIMITER $$

DROP PROCEDURE IF EXISTS `bankman`.`RefineData` $$
CREATE PROCEDURE `bankman`.`RefineData` ()
BEGIN
  Create Temporary Table Temp As (Select Distinct BankName FROM BankList);
  DECLARE R_N_F INTEGER DEFAULT 0;
  DECLARE BankNames CURSOR FOR SELECT * FROM Temp;
  DECLARE Bank_Name VARCHAR(100) DEFAULT "";
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET R_N_F = 1;
  OPEN BankNames;
    allBanks: LOOP
      FETCH BankNames INTO Bank_Name;
      IF R_N_F THEN
        LEAVE allStudents;
      END IF;
      DECLARE Bank_URL VARCHAR(100) DEFAULT NULL;
      DECLARE Bank_Login_URL VARCHAR(100) DEFAULT NULL;
      Select BankURL INTO Bank_URL From (Select Distinct BankURL, Length(BankURL) As Rank From BankList Where BankName = Bank_Name Order By Rank ASC Limit 1) As a;
      Select BankURL INTO Bank_Login_URL From (Select Distinct BankURL, Length(BankURL) As Rank From BankList Where BankName = Bank_Name Order By Rank DESC Limit 1) As b;
      Insert Into RefinedBankList Select Bank_Name, Bank_URL, Bank_Login_URL;
    END LOOP allBanks;
  CLOSE BankNames;
Drop Table Temp;

Open in new window

0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

waqarkhan88Author Commented:
Latest Code below:
DELIMITER $$

DROP PROCEDURE IF EXISTS `bankman`.`RefineData` $$
CREATE PROCEDURE `bankman`.`RefineData` ()
BEGIN
  Create Temporary Table Temp As (Select Distinct BankName FROM BankList);
  DECLARE R_N_F INTEGER DEFAULT 0;
  DECLARE BankNames CURSOR FOR SELECT * FROM Temp;
  DECLARE Bank_Name VARCHAR(100) DEFAULT "";
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET R_N_F = 1;
  OPEN BankNames;
    allBanks: LOOP
      FETCH BankNames INTO Bank_Name;
      IF R_N_F THEN
        LEAVE allStudents;
      END IF;
      DECLARE Bank_URL VARCHAR(100) DEFAULT NULL;
      DECLARE Bank_Login_URL VARCHAR(100) DEFAULT NULL;
      Select BankURL INTO Bank_URL From (Select Distinct BankURL, Length(BankURL) As Rank From BankList Where BankName = Bank_Name Order By Rank ASC Limit 1) As a;
      Select BankURL INTO Bank_Login_URL From (Select Distinct BankURL, Length(BankURL) As Rank From BankList Where BankName = Bank_Name Order By Rank DESC Limit 1) As b;
      Insert Into RefinedBankList Select Bank_Name, Bank_URL, Bank_Login_URL;
    END LOOP allBanks;
  CLOSE BankNames;
Drop Table Temp;
END $$

DELIMITER ;

Open in new window

0
SoLostCommented:
I got around that error by re-ordering the DECLARE statements to be at the top.  Look at my solution again.
0
mkiredjianCommented:
Ok waqarkhan88 your problem is you want to create the temporary table in your procedure and loop through it this is not possible. The solution is you create another stored procedure that creates this temporary table and call this procedure in your procedure and loop through the temporary table this will solve the problem: like this

DELIMITER $$

DROP PROCEDURE IF EXISTS `bankman`.`banknames_tmp` $$
CREATE PROCEDURE `bankman`.`banknames_tmp` ()
  Create Temporary Table Temp As
    (Select Distinct BankName FROM BankList);
END $$

DELIMITER ;

Next in your procedure:

DELIMITER $$

DROP PROCEDURE IF EXISTS `bankman`.`RefineData` $$
CREATE PROCEDURE `bankman`.`RefineData` ()
BEGIN
  DECLARE R_N_F INTEGER DEFAULT 0;
  DECLARE BankNames CURSOR FOR SELECT * FROM Temp;
  DECLARE Bank_Name VARCHAR(100) DEFAULT "";
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET R_N_F = 1;
  call banknames_tmp();
  OPEN BankNames;
    allBanks: LOOP
      FETCH BankNames INTO Bank_Name;
      IF R_N_F THEN
        LEAVE allBanks;
      END IF;
      DECLARE Bank_URL VARCHAR(100) DEFAULT NULL;
      DECLARE Bank_Login_URL VARCHAR(100) DEFAULT NULL;
      Select BankURL INTO Bank_URL From (Select Distinct BankURL, Length(BankURL) As Rank From BankList Where BankName = Bank_Name Order By Rank ASC Limit 1) As a;
      Select BankURL INTO Bank_Login_URL From (Select Distinct BankURL, Length(BankURL) As Rank From BankList Where BankName = Bank_Name Order By Rank DESC Limit 1) As b;
      Insert Into RefinedBankList Select Bank_Name, Bank_URL, Bank_Login_URL;
    END LOOP allBanks;
  CLOSE BankNames;
Drop Table Temp;
END $$

DELIMITER ;






0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.