problem creating procedure

[schilly]

I'm trying to create this procedure:

CREATE PROCEDURE make_intervals(startdate Date, enddate Date, intval integer, unitval varchar(10))
BEGIN
-- *************************************************************************
-- Procedure: make_intervals()
--    Author: Ron Savage
--      Date: 02/03/2009
--
-- Description:
-- This procedure creates a temporary table named time_intervals with the
-- interval_start and interval_end fields specifed from the startdate and
-- enddate arguments, at intervals of intval (unitval) size.
-- *************************************************************************
   declare thisDate Date;
   declare nextDate Date;
   set thisDate = startdate;

   -- *************************************************************************
   -- Drop / create the temp table
   -- *************************************************************************
   drop temporary table if exists time_intervals;
   create temporary table if not exists time_intervals
      (
      interval_start Date,
      interval_end Date
      );

   -- *************************************************************************
   -- Loop through the startdate adding each intval interval until enddate
   -- *************************************************************************
   repeat
      select
         case unitval
            when 'MICROSECOND' then timestampadd(MICROSECOND, intval, thisDate)
            when 'SECOND'      then timestampadd(SECOND, intval, thisDate)
            when 'MINUTE'      then timestampadd(MINUTE, intval, thisDate)
            when 'HOUR'        then timestampadd(HOUR, intval, thisDate)
            when 'DAY'         then timestampadd(DAY, intval, thisDate)
            when 'WEEK'        then timestampadd(WEEK, intval, thisDate)
            when 'MONTH'       then timestampadd(MONTH, intval, thisDate)
            when 'QUARTER'     then timestampadd(QUARTER, intval, thisDate)
            when 'YEAR'        then timestampadd(YEAR, intval, thisDate)
         end into nextDate;

      insert into time_intervals select thisDate, timestampadd(MICROSECOND, -1, nextDate);
      set thisDate = nextDate;
   until thisDate >= enddate
   end repeat;

END

 

which I got from http://stackoverflow.com/questions/510012/get-a-list-of-dates-between-two-dates

 

When I run this however, I receive "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 'PROCEDURE make_intervals(startdate Date, enddate Date, intval integer, unitval v' at line 1"

 

Any ideas?

 

I thought it might be a delimiter issue but doing this:

delimiter //
CREATE PROCEDURE make_intervals(startdate Date, enddate Date, intval integer, unitval varchar(10))
BEGIN
-- *************************************************************************
-- Procedure: make_intervals()
--    Author: Ron Savage
--      Date: 02/03/2009
--
-- Description:
-- This procedure creates a temporary table named time_intervals with the
-- interval_start and interval_end fields specifed from the startdate and
-- enddate arguments, at intervals of intval (unitval) size.
-- *************************************************************************
   declare thisDate Date;
   declare nextDate Date;
   set thisDate = startdate;

   -- *************************************************************************
   -- Drop / create the temp table
   -- *************************************************************************
   drop temporary table if exists time_intervals;
   create temporary table if not exists time_intervals
      (
      interval_start Date,
      interval_end Date
      );

   -- *************************************************************************
   -- Loop through the startdate adding each intval interval until enddate
   -- *************************************************************************
   repeat
      select
         case unitval
            when 'MICROSECOND' then timestampadd(MICROSECOND, intval, thisDate)
            when 'SECOND'      then timestampadd(SECOND, intval, thisDate)
            when 'MINUTE'      then timestampadd(MINUTE, intval, thisDate)
            when 'HOUR'        then timestampadd(HOUR, intval, thisDate)
            when 'DAY'         then timestampadd(DAY, intval, thisDate)
            when 'WEEK'        then timestampadd(WEEK, intval, thisDate)
            when 'MONTH'       then timestampadd(MONTH, intval, thisDate)
            when 'QUARTER'     then timestampadd(QUARTER, intval, thisDate)
            when 'YEAR'        then timestampadd(YEAR, intval, thisDate)
         end into nextDate;

      insert into time_intervals select thisDate, timestampadd(MICROSECOND, -1, nextDate);
      set thisDate = nextDate;
   until thisDate >= enddate
   end repeat;

END//

I receive the same error: "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 'PROCEDURE make_intervals(startdate Date, enddate Date, intval integer, unitval v' at line 1"

 

Thanks.

 

[Mchl]

Are you running it through phpMyAdmin?

If yes, there's an input box below SQL input box, whre you need to enter your delimiter (as defined in the first line), and paste the SQL without DELIMITER statement.

[schilly]

I'm using Sequel Pro but tried through command line as well with the same results.

 

I'll try through phpmyadmin with the delimiter option now.

[schilly]

hmmm no luck in phpmyadmin 2.8.2.4. There wasn't any spot for entering the delimiter.

[Mchl]

I did run it with no problems using DELIMITER || and END ||

 

Which MySQL version you use?

[schilly]

Looks like 4.1.22

 

Is Procedure only available in 5+? I'm guessing that's why it's complaining about the Procedure line.

 

How is procedure different from function?

[Mchl]

Sorry, no stored routines (neither procedures nor functions) for you. They come with MySQL 5.x

 

A function returns a value. You can use it in query like

SELECT ID, MyFunction(column1,column2) AS someAlias FROM someTable

 

Procedures on the other hand do not return a value, but perform some operations on data. You have to CALL them.

 

http://dev.mysql.com/doc/refman/5.0/en/stored-programs-views.html

[schilly]

grrrr damn. guess i'll have to look into upgrading.

 

thanks for the help.

[Mchl]

Yeah, high time. MySQL 4.x hasn't been supported for like a year now?

[schilly]

Yeah upgraded.

 

Our db seems to be running much faster.

 

Was their a lot of performance improvements for 4.x->5.x?

[Mchl]

There was a lot of improvements in almost all aspects of MySQL. The storage engines got their share of upgrades too (some recent upgrades to InnoDB are especially noteworthy).