Using a delimiter within a prepared statement in a procedure

Question

EDIT: PLEASE NOTE: The thing I ask for is impossible, as explained at the bottom! Therefore, the question is answered.

Dear people with overflowing stacks,

I have a problem with a syntax error in a prepared statement in a stored procedure. I get a syntax error when I run the procedure, but not if I manually execute the syntax returned by the "SELECT @sql;" command. It has to do with the delimiter, because if I have only one command, it runs fine within the procedure. I did not use any table within my procedure for easier reproduction:

DROP PROCEDURE IF EXISTS stackoverflow_test;
DELIMITER $$

CREATE PROCEDURE stackoverflow_test ()
BEGIN   
    SET @sql = CONCAT('
        SELECT "test12" AS test1;
        SELECT "test34" AS test2;
    ');

    #return the statement from @sql
    SELECT @sql;

    #execute the statement from @sql
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

CALL stackoverflow_test ();

I tried to look for this, but all hits are about the general use of the DELIMITER command when defining a procedure. Now I know that the delimiter works client-side. Do I have to use something else in a statement that is saved on the server? EDIT: I did try to use $$ as a delimiter, which produced the same syntax error.

Best chonez

EDIT: It seems to be impossible. "SQL syntax for prepared statements does not support multi-statements" Source: http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-prepared-statements.html . The solution for me was to reduce the prepared statement to one statement and move the other statements around the execution, which worked for me because the other statements are static:

#first static statement
DROP TABLE IF EXISTS `table`;

#dynamic statement
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

#second static statement
ALTER TABLE `table`
    ADD UNIQUE INDEX sdef ( `cntry`, `track`, `year` );

Answer 1

You are changing the delimiter to $$ at start.

DELIMITER $$

and you need to use it in your sql like this:

SET @sql = CONCAT('
    SELECT "test12" AS test1$$
    SELECT "test34" AS test2$$
');

and the other thing:

SET @sql = CONCAT('
    SELECT "test12" AS test1;
    SELECT "test34" AS test2;
');

is also not true, try this way:

SET @sql = 'SELECT "test12" AS test1$$SELECT "test34" AS test2$$';

Answer 2

As explained in the main post, it seems to be impossible: "SQL syntax for prepared statements does not support multi-statements"

Source: http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-prepared-statements.html