Stored proc 1

[SQLScholar]

Hey all,

I am running Mysql version 5.0.77 and phpMyAdmin - 2.8.2.4

I cant seem to create stored procs. I am even trying a test one from this website:

http://dev.mysql.com/tech-resources/articles/mysql-storedprocedures.html

Slightly changes (removed the insert) i have:

CREATE PROCEDURE procedure1                /* name */
(IN parameter1 INTEGER)                    /* parameters */
BEGIN                                      /* start of block */
  DECLARE variable1 CHAR(10);                /* variables */
  IF parameter1 = 17 THEN                    /* start of IF */
    SET variable1 = 'birds';                   /* assignment */
  ELSE
    SET variable1 = 'beasts';                  /* assignment */
  END IF;                                   /* end of IF */
 END                                       /* end of block */

And I am gettting:

Error
SQL query:

CREATE PROCEDURE procedure1/* name */(

IN parameter1 INTEGER
)/* parameters */ BEGIN /* start of block */DECLARE variable1 CHAR( 10 ) ;

MySQL said:

#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 '' at line 4


Any help for a Mysql newbie greatly recieved.

Dan






----------------------------------------

http://www.cardboardcoder.com

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[KarveR]

I'd drop all the comments and see if you get further, also you may need to change teh delimiters as you load stored procs.

heres a sample.

DELIMITER //
CREATE PROCEDURE do_thing(Field_ID varchar(15))
LANGUAGE SQL
NOT DETERMINISTIC
COMMENT 'Undo Exported date and time'
BEGIN

UPDATE Table1
SET
some_date='0000-00-00',
some_time='00:00:00'
WHERE
myfield=Filed_ID;
END;
//
DELIMITER ;

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[SQLScholar]

Hi,

Thanks for the response.

I tried using your exact query (i would be glad to be able to do ANY proc at the moment) and i get

#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 'DELIMITER //
CREATE PROCEDURE do_thing(Field_ID varchar(15))
LANGUAGE SQL' at line 1

Any ideas?

Dan

----------------------------------------

http://www.cardboardcoder.com

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[KarveR]

much simplified to start you off then ,

mysql> delimiter //
mysql> CREATE PROCEDURE procedure1(IN param1 INTEGER)
-> LANGUAGE SQL
-> NOT DETERMINISTIC
-> BEGIN
-> select
-> IF(param1="17",'birds','beasts');
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call procedure1("12");
+----------------------------------+
| IF(param1="17",'birds','beasts') |
+----------------------------------+
| beasts |
+----------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call procedure1("17");
+----------------------------------+
| IF(param1="17",'birds','beasts') |
+----------------------------------+
| birds |
+----------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)


______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[SQLScholar]

Thanks mate.

Got it working - it was all to do with the delimter bit and running it from mysql php. Thanks.

----------------------------------------

http://www.cardboardcoder.com

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------