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

Writing script in MySQL Workbench SQL Editor

I Have a routine that works but is ginving me an unexpected result.  I'm trying to debug the sql a routine in the MySQL Editor in MySQL Workbench 5.2.16.  I tried taking the SQL from the routine definition and pasting it into the editor.  I added a line at the beginning for my input parameter.  It will not run because of syntax errors.  The DECLARE statements and the IF statements seem to be unacceptable in the editor.  Here I have embedded an image of the editor window that shows the syntax checking.  snapshot of editor windowThe red squgglies indicate syntax errors.

Is there a reference that states what are acceptable sql statements in the editor?
0
pbouchard1
Asked:
pbouchard1
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
pbouchard1,

I don't know of a list, but my understanding is that aside from user-defined variables the only valid syntax is those that can execute as a single-line SQL statement which is why IF ... THEN won't work.  You can use case OR if statements as you would in a SQL query though.

Therefore, to test this you can do this.

Instead of:
DECLARE iSeasonYear INTEGER;
SET iSeasonYear = 2010;

Use:
SET @iSeasonYear = 2010;


You can create the variables that are results from queries on the fly using the INTO methodology you already have in place.


For the IF, you would do this in a SELECT.
SELECT CASE WHEN @WeekNum > 0 THEN @WeekNum WHEN ... THEN ... ELSE 17 END AS `WeekNum`

Hope that helps!

Regards,
Kevin
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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