Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2013
    Posts
    2

    Unanswered: WHILE loop doesn't work

    Hi,
    I posted this on MySQL's own forum almost two days ago but didn't got any reply so I will try here. Thanks in advance!

    Can anyone help and exlplain why this query doesn't work:
    Code:
    BEGIN 
    
    DROP TABLE Temp; 
    CREATE TEMPORARY TABLE Temp 
    (weeks INT); 
    
    SET @fromDate = WEEK('2013-01-07'); 
    SET @toDate = WEEK('2013-02-15'); 
    SET @counter = 0; 
    
    
    WHILE @counter < 5 DO 
    SET @counter = @counter + 1; 
    END WHILE; 
    
    
    END
    Everything is fine until the While loop. The error message that I recieve is:
    Code:
    "Query: WHILE @counter < 5 DO SET @counter = @counter + 1 
    
    Error Code: 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 'WHILE @counter < 5 DO 
    SET @counter = @counter + 1' at line 1 
    
    Execution Time : 0 sec 
    Transfer Time : 0 sec 
    Total Time : 0 sec"
    I have googled and checked the documentation (MySQL :: MySQL 5.5 Reference Manual :: 13.6.5.8 WHILE Syntax) and everything seems to be find however I must be missing something.

    Best regards,
    Tomas

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    WHILE is for use within stored programs

    see MySQL :: MySQL 5.5 Reference Manual :: 19 Stored Programs and Views
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2013
    Posts
    2
    Ok, thanks. But I still can't get it to work:
    Code:
    CREATE PROCEDURE emptyWeeks(p1 INT)
    
    BEGIN 
    
    	DROP TABLE Temp;
    	CREATE TEMPORARY TABLE Temp (weeks INT);
    
    	SET @fromDate = WEEK('2013-01-07');
    	SET @toDate = WEEK('2013-02-15');
    	SET @counter = 0;
    	
    
    	WHILE @counter < 5 DO
    		SET @counter = @counter + 1;
    	END WHILE;
    
    	
    END
    Two error messages:
    Code:
    Query: CREATE PROCEDURE emptyWeeks(p1 int) BEGIN DROP TABLE Temp
    
    Error Code: 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 '' at line 3
    
    Execution Time : 0 sec
    Transfer Time  : 0 sec
    Total Time     : 0 sec
    --------------------------------------------------
    Code:
    Query:  WHILE @counter < 5 DO SET @counter = @counter + 1
    
    Error Code: 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 'WHILE @counter < 5 DO
    		SET @counter = @counter + 1' at line 1
    
    Execution Time : 0 sec
    Transfer Time  : 0 sec
    Total Time     : 0 sec
    I already made what I want to do in MSSQL however using a procedure is not a problem but the MySQL code is very different and my DB manager (SQLYog) doesn't help me a lot.

    The MSSQL code:
    Code:
    DROP TABLE ##TEMP
    CREATE TABLE ##TEMP
    (week varchar(4),
    SKU varchar(100),
    total varchar(100))
    
    declare @counter int
    declare @sku varchar(50)
    	set @sku = 'MSC-4508Z'
    	set @counter = 50
    	while @counter <= 52
    	begin
    	  INSERT INTO ##TEMP (week)
    	  VALUES (@counter)
    
    	  set @counter = @counter + 1
    	end
    
    	set @counter = 50
    	while @counter <= 52
    	begin
    	  UPDATE ##TEMP
    	  SET 
    		SKU = @sku, 
    		total = 
    			isNull((	SELECT SUM(total) 
    				FROM OrderLines 
    				WHERE SKU = @sku 
    				AND Datepart(ww,ProcessedDate) = @counter 
    				GROUP BY Datepart(ww,ProcessedDate)
    			), 0)
    			WHERE week = @counter
    
    	  set @counter = @counter + 1
    	end
    	
    	SELECT week, SKU, total FROM ##TEMP
    Best regards,
    KFluffie

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •