CODEWITHSUNDEEP
mysqlstored-proceduresexecuteprepare
Huckleberry
Huckleberry

Reputation: 31

In MySql, how can you Prepare and Execute a Create Procedure

I'm attempting to create a MySql Stored Procedure via a Prepare/ Execute statement and I'm getting a error. I don't understand what Syntax I'm breaking.

SET @SourceDBName='dev';
    SET @alterstatement=CONCAT('DELIMITER //
    CREATE PROCEDURE ',@SourceDBName,'.execute_statement (alterstatement VARCHAR (767)) 
    BEGIN
        SELECT @alterstatement; 
        PREPARE executionstatement FROM @alterstatement; 
        EXECUTE executionstatement;
        END //');
    SELECT @alterstatement;
    PREPARE executionstatement FROM @alterstatement;
    EXECUTE executionstatement;

I get a generic syntax error:

09:43:20    PREPARE executionstatement FROM @alterstatement Error 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 'DELIMITER //         CREATE PROCEDURE dev.execute_statement (alterstatement VARC' at line 1    0.086 sec

Can someone tell me what I'm doing wrong? I've tried troubleshooting, but I haven'y figured it out yet.

Upvotes: 0

Views: 856

Answers (1)

Álvaro González
Álvaro González

Reputation: 146460

DELIMITER is a command of the official command-line utility. You can't use it elsewhere.

In your case, you don't need it anyway. The PREPARE statement does not accept multiple statements:

The text must represent a single statement, not multiple statements.

... so there's no need for separators. However, you'll get this error:

This command is not supported in the prepared statement protocol yet

... because CREATE PROCEDURE is apparently not supported yet:

The following SQL statements can be used as prepared statements:

[...]

{CREATE | DROP} INDEX
{CREATE | RENAME | DROP} DATABASE
{CREATE | DROP} TABLE
{CREATE | RENAME | DROP} USER
{CREATE | DROP} VIEW

Upvotes: 1

Related Questions