Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

Can't run create procedure as query

DaedalusAlpha's profile image DaedalusAlpha posted 1 month ago in Running SQL scripts Permalink

Hello,

I'm not very experienced with MySQL or HeidiSQL so this might be pretty obvious but the problem I'm having is this:

I'm using the create new -> stored routine in HeidiSQL to create a new stored routine. This works fine. However, I was expecting it to be possible to copy the code shown in the "CREATE code" tab in the routine and paste it in an SQL file or into an empty query tab and run it from there. But when I do that I start getting syntax errors.

This is the code exactly copied from the CREATE code tab:

CREATE DEFINER=`user`@`%` PROCEDURE `add_rev`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN

    -- Rollback on error
    DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;

    START TRANSACTION;

        CREATE TABLE IF NOT EXISTS db_rev(revision INT NOT NULL UNIQUE);

        INSERT INTO db_rev VALUES (1);

        -- Add commands here



    COMMIT;

END

The complaining starts at DECLARE EXIT HANDLER ... and if I remove that it jumps somewhere else.

To me, it seems like all of the code isn't read as one entity and that's what messes things up. But I don't know how to make HeidiSQL understand what I want it to do.

DaedalusAlpha's profile image DaedalusAlpha posted 1 month ago Permalink

Nevermind, I'm an idiot. There is a dropdown next to the blue run arrow where you can select to send the batch in one go. With that selected it all works fine.

ansgar's profile image ansgar posted 1 month ago Permalink

Additionally, procedures and some other items can contain compound statements. Which means you have statements within one CREATE statement. That's why the semicolon makes problems, if you don't tell HeidiSQL to change it:

DELIMITER \\
CREATE PROCEDURE ...\\
DELIMITER ;

Please login to leave a reply, or register at first.




Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.