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;

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

If I am not wrong, are we not supposed to prefix the variable name with @ symbol like
declare @var1 INT

hope it helps

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.