Solution to the problem of pre-indentation when DELIMITER executes stored procedures in SQLyog

The root of the problem

1. SQLyog's statement separating logic：

   • SQLyog will default to semicolons (;) and line breaks automatically separate SQL statements

   • SQLyog may incorrectly recognize the indented content as a new SQL statement when the code has indented

2. DELIMITER's sensitivity：

   • DELIMITER //The command must be independent of one line and no leading spaces

   • Any indentation will cause SQLyog to treat it as plain text rather than directive

3. Statements in stored procedures：

   • Indentation in the process body will not affect execution (because it is parsed as a whole)

   • But the process external commands (such as DELIMITER) must be indented without indentation

Example of correct writing

-- Must be written in the top（No indentation）
DELIMITER //
CREATE PROCEDURE grant_role_to_all_users()
BEGIN
    -- Indentation here will not affect execution
    DECLARE finished INTEGER DEFAULT 0;
    DECLARE user_host VARCHAR(255);
    
    DECLARE user_cursor CURSOR FOR 
        SELECT CONCAT('`',USER,'`@`',HOST,'`') 
        FROM  
        WHERE USER NOT IN ('root','','','');
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
    
    OPEN user_cursor;
    
    get_user: LOOP
        FETCH user_cursor INTO user_host;
        IF finished = 1 THEN LEAVE get_user; END IF;
        
        SET @grant_sql = CONCAT('GRANT "public_role" TO ', user_host);
        PREPARE stmt FROM @grant_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        SET @default_sql = CONCAT('SET DEFAULT ROLE "public_role" FOR ', user_host);
        PREPARE stmt FROM @default_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP get_user;
    
    CLOSE user_cursor;
END//
-- Must be written in the top（No indentation）
DELIMITER ;

Permanent solution

1. Turn off SQLyog's automatic separation function：

   • Menu Bar → Tools → Preferences → Query Editor

   • Uncheck "Auto-separate SQL statements"

2. Using batch execution mode：

   • Select all codes (including DELIMITER)

   • according toCtrl+Shift+F9(Batch execution) rather than normal execution

3. Check editor settings：

   • Make sure the "Smart Indent" or "Syntax Aware Indent" function is not enabled

Why is the command line not affected?

MySQL native command line client:

• Rely on DELIMITER instructions completely

• Don't care about indentation and formatting

• It will not be executed until the defined ending character (such as //) is encountered.

In order to improve ease of use, graphics tools such as SQLyog will try to automatically parse SQL structures, which will lead to format sensitivity.

Best Practice Recommendations

1. Stored procedure creation statement suggestions:

   • DELIMITER and END// Must be top-end

   • Indentation in the process body remains consistent (4 spaces or 1 tab)

2. Complex process suggestions:

-- Top
DELIMITER //
CREATE PROCEDURE proc_name()
BEGIN
    -- Indent content
    SELECT * FROM table;
END//
-- Top
DELIMITER ;

Save as a script file and execute it through the command line:

mysql -u user -p db_name < 

This is the article about the solution to the pre-indent problem that occurs when DELIMITER executes stored procedures in SQLyog. This is the end. For more related SQLyog DELIMITER, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!