Failing MySQL Snippet

I've got the following snippet(part) for MySQL:

Code:

$$
SELECT count(1) INTO @tabla FROM information_schema.`TABLES` AS t
WHERE 1 = 1
AND t.TABLE_SCHEMA = '$DB$'
AND t.TABLE_NAME = '$OBJECT(view, proc, func)$'
AND t.TABLE_TYPE = 'BASE TABLE';

SELECT count(1) INTO @nezet FROM information_schema.`TABLES` AS t
WHERE 1 = 1
AND t.TABLE_SCHEMA = '$DB$'
AND t.TABLE_NAME = '$OBJECT(view, proc, func)$'
AND t.TABLE_TYPE = 'VIEW';

SELECT count(1) INTO @eljaras FROM mysql.proc AS p
WHERE 1 = 1
AND p.db = '$DB$'
AND p.name = '$OBJECT(view, proc, func)$'
AND p.`type` = 'PROCEDURE';

SELECT count(1) INTO @fuggveny FROM mysql.proc AS p
WHERE 1 = 1
AND p.db = '$DB$'
AND p.name = '$OBJECT(view, proc, func)$'
AND p.`type` = 'FUNCTION';

SELECT '$DB$', @tabla, @nezet, @eljaras, @fuggveny
$$

It fails with

	Quote:
	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 'SELECT count(1) INTO @nezet FROM information_schema.`TABLES` AS t WHERE 1 = 1 A' at line 6 Executing user-defined query in line 1 ... Failed

when I trigger it. Having only one of the statements (any of them) will make it execute successfully. The whole shebang runs from the editor as well (after replacing the corresponding SA macros, of course).

Any hints on the reason?

Here is what happens behind the scenes. When you run this same SQL block from the editor, using either the editor's Execute facility or SQL Assistant's Execute SQL Code menu, they parse your SQL statements, find semicolons, which are are used as statement separators, and then execute each individual statement separately. And everything is fine in that case.

When you put multiple statements inside the same $$..$$ macro, the entire block is executed as is, as a single SQL query send to the database leading to MySQL Code 1064 error. Please note that as compared to SQL Server, and some other databases MySQL provides very limited support for SQL batches. In most cases they can be used inside procedural code only.

The good news is that the solution to your case is very simple, wrapping each SQL statement into a separate $$..$$ macro should do it. You can have multiple $$.$$ macros within the same code snippet. Note that ending semicolons are optional, they are stripped anyway.

Ahh, that explains a lot. The suggested solution works and to my big surprise it retains the values of the variables through the entire snippet as well. Thank you very much!