[RESOLVED] Can't see the problem in this procedure

mrbaseball34

For the life of me, I can't see any errors in this procedure script but it claims I
have an error:

SQL Error (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 ';    SELECT lookup_manufacturer(manufacturer) INTO _manufacturerID;     '
at line 175

Can anyone see the problem?

IF (LENGTH(_subcategory) > 0) AND (_categoryID <> 0) THEN
BEGIN
  SELECT lookup_category(_subcategory) INTO _subcategoryID;
  IF (_subcategoryID > 0) THEN
  BEGIN
    SELECT add_sub_category(_categoryID, _subcategory) INTO _subcategoryID;
    SELECT add_category_group(_subcategoryID); 
    SELECT add_category_lang(_subcategoryID, _subcategory); 
  END;
END;

SELECT lookup_manufacturer(manufacturer) INTO _manufacturerID;

IF (_manufacturerID = 0) AND (LENGTH(manufacturer) > 0) THEN
BEGIN
 SELECT add_manufacturer(manufacturer) INTO _manufacturerID;
END;

note:
manufacturer is the value returned from the cursor, if there was a _manufacturer would be a local variable.

I am not manipulating the manufacturer at all so at the time I'm calling lookup_manufacturer, manufacturer is set to the value from the cursor and _manufacturer is not a valid local variable and is it never used.

The full procedure script is located here (748 lines):
http://www.mechanictoolsonline.com/ImportToolWeb.sql

Lars_Berg

if statements should be ended with end if. You don't need the begin end blocks.

mrbaseball34

Lars Berg;10954725 wrote:
if statements should be ended with end if. You don't need the begin end blocks.

If that is the case, then why didn't it give me an error on the previous if..end:

bradgrafelman

Why question how long it took the SQL parser to find your error? :p

Lars is correct - the IF statements should be closed with an 'END IF;' and not use the "BEGIN..END" syntax. Read the MySQL manual if you don't believe us.

mrbaseball34

It's not that I didn't believe you...

I changed them all and it parses...well, almost.

I am getting an error on the REPEAT..UNTIL done END REPEAT;

I can't figure it out. I literally copied the structure for my repeat loop out of the manual.
I uploaded the new changed version to the link above, can you see why I'm getting this error?

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 'done   END REPEAT;

bradgrafelman

Perhaps try using:

UNTIL done <> 0

instead?

mrbaseball34

UNTIL done <> 0

Same error...

mrbaseball34

one thing I didn't do was declare a continue handler and open the cursor but it is still giving me the error after I did that.

mrbaseball34

One final thing. There were several IF..ELSE that didn't have closing END IF;

I have it working now...

Thanks for the help, this is now resolved.