Difficulty in writing Stored Procedure in MYSQL

Question

The code my Stored Procedure is:

CREATE PROCEDURE insert_sait_inf(IN name TEXT,IN title TEXT,IN link TEXT)

BEGIN

INSERT INTO sait_inf(sait_name,sait_title,sait_link) VALUES (name,title,link);

END

But the error is:

  #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 3 

Answer 1

As documented under Defining Stored Programs:

• If you use the mysql client program to define a stored program containing semicolon characters, a problem arises. By default, mysql itself recognizes the semicolon as a statement delimiter, so you must redefine the delimiter temporarily to cause mysql to pass the entire stored program definition to the server.

  The same is true of phpMyAdmin.

• To redefine the mysql delimiter, use the delimiter command.

  Not all versions of phpMyAdmin recognise the delimiter command; instead, one can use the Delimiter input box beneath the SQL input box: see How do I write an SP in phpMyAdmin (MySQL)?

• The following is an example of a function that takes a parameter, performs an operation using an SQL function, and returns the result. In this case, it is unnecessary to use delimiter because the function definition contains no internal ; statement delimiters:

  mysql> CREATE FUNCTION hello (s CHAR(20))
  mysql> RETURNS CHAR(50) DETERMINISTIC
      -> RETURN CONCAT('Hello, ',s,'!');
  Query OK, 0 rows affected (0.00 sec)
  

  In your case, you can remove the BEGIN ... END compound statement block and avoid the need to change delimiter.