Condition IF ELSE AT WHILE

Dev Shed Forums Sponsor:

January 10th, 2013, 06:19 PM

oliwin

Registered User

Join Date: Jan 2013

Posts: 14

Time spent in forums: 4 h 20 m 7 sec
Reputation Power: 0

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.

January 10th, 2013, 08:24 PM

spacebar208

Contributing User

Join Date: Apr 2012

Location: spaceBAR Central

Posts: 191

Time spent in forums: 2 Days 9 h 59 m 25 sec
Reputation Power: 41

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).

January 11th, 2013, 05:08 AM

oliwin

Registered User

Join Date: Jan 2013

Posts: 14

Time spent in forums: 4 h 20 m 7 sec
Reputation Power: 0

Quote:

Originally Posted by spacebar208

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

January 11th, 2013, 05:28 AM

Jacques1

pollyanna

Join Date: Jul 2012

Location: Germany

Posts: 1,881

Time spent in forums: 1 Month 2 Weeks 2 Days 8 h 29 m 53 sec
Reputation Power: 813

Code:

DELIMITER<blank>;

January 11th, 2013, 05:51 AM

oliwin

Registered User

Join Date: Jan 2013

Posts: 14

Time spent in forums: 4 h 20 m 7 sec
Reputation Power: 0

Quote:

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

January 11th, 2013, 05:56 AM

Jacques1

pollyanna

Join Date: Jul 2012

Location: Germany

Posts: 1,881

Time spent in forums: 1 Month 2 Weeks 2 Days 8 h 29 m 53 sec
Reputation Power: 813

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.

January 11th, 2013, 06:37 AM

oliwin

Registered User

Join Date: Jan 2013

Posts: 14

Time spent in forums: 4 h 20 m 7 sec
Reputation Power: 0

Quote:

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

January 19th, 2013, 01:29 PM

f_razzoli

Contributing User

Join Date: Jan 2013

Location: Italy

Posts: 36

Time spent in forums: 4 h 37 m 48 sec
Reputation Power: 1

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.

Condition IF ELSE AT WHILE

Developer Shed Advertisers and Affiliates