Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2008
    Posts
    2

    Post Unanswered: Problem in Declare statement

    Hi all,
    here i am manually migrating the Mssql stored procedure to Mysql Stored procedure. when running the code i am getting the following error at declare statement..

    error

    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 'INT'

    here i am pasting my stored procedure please chek and tell me where is the error



    CREATE PROCEDURE DELETE_GAMES(in event_id_inp INT,in modified_by_inp VARCHAR(100))
    BEGIN
    DECLARE level_tmp INT ;
    SELECT level into level_tmp
    FROM event
    WHERE event_id = event_id_inp;
    DROP TABLE IF EXISTS event_tmp ;
    create table event_tmp (event_id INT,level INT);

    IF level_tmp = 3 then
    UPDATE event
    SET event_status = 'D',
    modified_by = modified_by_inp
    WHERE event_id = event_id_inp
    end if;

    IF level_tmp = 2 then

    INSERT INTO event_tmp
    event_id
    SELECT event_id FROM event
    WHERE parent_id = event_id_inp

    UPDATE evt
    SET event_status = 'D',
    modified_by = modified_by_inp
    FROM event evt, @event_tmp tmp
    WHERE evt.event_id = tmp.event_id

    UPDATE event
    SET event_status = 'D',
    modified_by = modified_by_inp
    WHERE event_id = event_id_inp
    END if;

    IF level_tmp = 1 then

    INSERT INTO event_tmp
    event_id, level
    SELECT event_id, level FROM event
    WHERE parent_id = event_id_inp

    INSERT INTO event_tmp
    event_id, level
    SELECT evt.event_id, evt.level FROM event evt, event_tmp tmp
    WHERE evt.parent_id = tmp.event_id

    UPDATE evt
    SET event_status = 'D',
    modified_by = modified_by_inp
    FROM event evt, event_tmp tmp
    WHERE evt.event_id = tmp.event_id

    UPDATE event
    SET event_status = 'D',
    modified_by = modified_by_inp
    WHERE event_id = event_id_inp
    END if;
    END;

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Is it "your" stored procedure, or a stored procedure accquired from elsewhere?

    did reading the MySQL manual give you any clues or suggestions as to what may have failed
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I'm guessing here:
    Put the following commands before and after your procedure code.
    Code:
    show warnings;
    delimiter $
    [your procedure goes here]
    $
    MySQL uses the ";" character to delimit each SQL statement ie every time it finds a ";" then it tries to run the SQL it has so far. In stored procs you need to place a whole bunch of SQL statements together and have them run as one unit. Therefore you need to change the delimiter character before you run your SQL through and you'll want to put the new delimiter after the stored proc ie :

  4. #4
    Join Date
    Nov 2008
    Posts
    23
    If I am not wrong, are we not supposed to prefix the variable name with @ symbol like
    declare @var1 INT

    hope it helps

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by kjambu
    If I am not wrong, are we not supposed to prefix the variable name with @ symbol like
    declare @var1 INT
    True in Sybase etc but not true in MySQL. Though it's always a good idea to prefix your variables with some standard notation - you could use l for a local variable and then i for int so you'd have something like li_var1.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •