#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    14
    Rep Power
    0

    Exclamation Condition IF ELSE AT WHILE


    Hello! I tried any ways to resolve SQL error in the this query:
    Code:
    DELIMITER $$
    DROP PROCEDURE IF EXISTS test$$
    CREATE PROCEDURE test()
    BEGIN
    DECLARE count INT DEFAULT 0;
    DECLARE num INT DEFAULT 0;
    DECLARE param INT DEFAULT 0;
    
    
    WHILE count < 9 DO
    
    IF num = 3 THEN 
    SET param = param + 1;
    SET num =0;
    END IF;
    
    SELECT * FROM instans_type WHERE block = param;
    SET count = count + 1;
    SET num = num + 1;
            
    END WHILE;
    
    END$$
    DELIMITER;
    My phpmyadmin says that are sintax errors, WTF?
    Please, help me.
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    spaceBAR Central
    Posts
    229
    Rep Power
    42
    Are they getting the error when compiling the procedure or when running it?

    Either way, Get them to give you the detailed error text and if it's when they are running it make sure they give you the line number(s).
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    14
    Rep Power
    0
    Originally Posted by spacebar208
    Are they getting the error when compiling the procedure or when running it?

    Either way, Get them to give you the detailed error text and if it's when they are running it make sure they give you the line number(s).
    SQL-query:

    DELIMITER;


    Answer MySQL:

    #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 'DELIMITER' at line 1
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Code:
    DELIMITER<blank>;
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    14
    Rep Power
    0
    Originally Posted by Jacques1
    Code:
    DELIMITER<blank>;
    If drop DELIMITER then writes

    #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 'CREATE PROCEDURE test()
    BEGIN
    DECLARE count INT DEFAULT 0' at line 2
  10. #6
  11. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Who said anything about dropping "DELMITER"? I'm telling you that the syntax is
    Code:
    DELIMITER(then a space)(then a semicolon)
    Without the parentheses, of course.
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    14
    Rep Power
    0
    Originally Posted by Jacques1
    Who said anything about dropping "DELMITER"? I'm telling you that the syntax is
    Code:
    DELIMITER(then a space)(then a semicolon)
    Without the parentheses, of course.
    Okay, thank you for help. Works. But dont understand why returns empty results?
    I have at table instans_type notes with block = 1, block = 2, block = 3, block = 4, block = 5 and i wanna to choose only 3 notes for every block
    111
    222
    333
    444
    555
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Location
    Italy
    Posts
    36
    Rep Power
    2
    You need to read better the manual about the Stored Procedures.


    * Your SELECT does nothing. It is executed, but you do nothing with the result.
    * Stored procedures return nothing.
    * Even Stored Functions, return a single value, not a result set.

    You can insert the rows into a tamporary table, and then read it from outside the procedure.

IMN logo majestic logo threadwatch logo seochat tools logo