Error in MySQL 5.1 stored Procedure

Question

I am new to stored procedure

DELIMITER //
CREATE PROCEDURE sp_MyNewTable
(IN Mod nvarchar(50),IN Did int,IN startdate datetime,IN enddate datetime)
BEGIN
Declare DateDuration int,
SET actstatus=1,
SET DateDuration =  SELECT  DATEDIFF(startdate,enddate) as Datediff
insert into mytable (Duration,Module,Deptid,taskstartdate,activestatus) values       (DateDuration,Mod,did,enddate,startdate,actstatus)
 Select * from mytable 
 END //
 DELIMITER;

Getting error if I execute this:

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 sp_MyNewTable (IN Mod nvarchar(50),IN Did int,IN startdate datetime,IN enddate datetime)

MYSQL version is MYSQL 5.1

Stored procedures were introduced in MySQL 5. Besides … are you really still on MySQL 4.x? Based on that, I'd assume that your OS is horribly outdated and has gaping security holes. — lxg

wchiquito wchiquito · Accepted Answer · 2014-09-15T14:11:17

Try:

DELIMITER //

CREATE PROCEDURE `sp_MyNewTable`(IN `Mod` VARCHAR(50),
                                 IN `Did` INT,
                                 IN `startdate` DATETIME,
                                 IN `enddate` DATETIME)
BEGIN
  DECLARE `DateDuration` INT;
  DECLARE `actstatus` DATETIME;
  SET `actstatus` := 1, `DateDuration` := DATEDIFF(`startdate`, `enddate`);
  INSERT INTO `mytable`
  (`Duration`, `Module`, `Deptid`, `taskenddate`, `taskstartdate`, `activestatus`)
  VALUES
  (`DateDuration`, `Mod`, `Did`, `enddate`, `startdate`, `actstatus`);
  SELECT * FROM `mytable`;
END//

DELIMITER ;

SQL Fiddle demo

Error in MySQL 5.1 stored Procedure

1 Answers