• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 461
  • Last Modified:

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

0
waqarkhan88
Asked:
waqarkhan88
  • 3
  • 2
1 Solution
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now