MySQL create stored procedure syntax with delimiter

Question

I am trying to create a stored procedure in MySQL using a delimiter like this:

use am;  DELIMITER $$  CREATE PROCEDURE addfields() BEGIN   DECLARE done INT DEFAULT FALSE;   DECLARE acc INT(16);   DECLARE validId INT DEFAULT 0;  END $$  DELIMITER ; 

It gives me an error:

#1304 - PROCEDURE addfields already exists 

What is the proper syntax for making a stored procedure with a delimiter and dropping it if it exists first?

Answer 1

Getting started with stored procedure syntax in MySQL (using the terminal):

1. Open a terminal and login to mysql like this:

el@apollo:~$ mysql -u root -p Enter password:  Welcome to the MySQL monitor.  Commands end with ; or \g. mysql>  

2. Take a look to see if you have any procedures:

mysql> show procedure status; +-----------+---------------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+ | Db        | Name          | Type      | Definer | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation | +-----------+---------------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+ |   yourdb  | sp_user_login | PROCEDURE | root@%  | 2013-12-06 14:10:25 | 2013-12-06 14:10:25 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  | +-----------+---------------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+ 1 row in set (0.01 sec) 

I have one defined, you probably have none to start out.

3. Change to the database, delete it.

mysql> use yourdb; Database changed  mysql> drop procedure if exists sp_user_login; Query OK, 0 rows affected (0.01 sec)      mysql> show procedure status; Empty set (0.00 sec)      

4. Ok so now I have no stored procedures defined. Make the simplest one:

mysql> delimiter // mysql> create procedure foobar()     -> begin select 'hello'; end// Query OK, 0 rows affected (0.00 sec) 

The // will communicate to the terminal when you are done entering commands for the stored procedure. the stored procedure name is foobar. it takes no parameters and should return "hello".

5. See if it's there, remember to set back your delimiter!:

 mysql> show procedure status;  ->   ->  

Gotcha! Why didn't this work? You set the delimiter to // remember? Set it back to ;

6. Set the delimiter back and look at the procedure:

mysql> delimiter ; mysql> show procedure status; +-----------+--------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+ | Db        | Name   | Type      | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation | +-----------+--------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+ | yourdb    | foobar | PROCEDURE | root@localhost | 2013-12-06 14:27:23 | 2013-12-06 14:27:23 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  | +-----------+--------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+ 1 row in set (0.00 sec)      

7. Run it:

mysql> call foobar(); +-------+ | hello | +-------+ | hello | +-------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) 

Hello world complete, lets overwrite it with something better.

8. Drop foobar, redefine it to accept a parameter, and re run it:

mysql> drop procedure foobar; Query OK, 0 rows affected (0.00 sec)  mysql> show procedure status; Empty set (0.00 sec)  mysql> delimiter // mysql> create procedure foobar (in var1 int)     -> begin select var1 + 2 as result;     -> end// Query OK, 0 rows affected (0.00 sec)  mysql> delimiter ; mysql> call foobar(5); +--------+ | result | +--------+ |      7 | +--------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) 

Nice! We made a procedure that takes input, modifies it, and does output. Now lets do an out variable.

9. Remove foobar, Make an out variable, run it:

mysql> delimiter ; mysql> drop procedure foobar; Query OK, 0 rows affected (0.00 sec)  mysql> delimiter // mysql> create procedure foobar(out var1 varchar(100))     -> begin set var1="kowalski, what's the status of the nuclear reactor?";     -> end// Query OK, 0 rows affected (0.00 sec)   mysql> delimiter ; mysql> call foobar(@kowalski_status); Query OK, 0 rows affected (0.00 sec)  mysql> select @kowalski_status; +-----------------------------------------------------+ | @kowalski_status                                    | +-----------------------------------------------------+ | kowalski, what's the status of the nuclear reactor? | +-----------------------------------------------------+ 1 row in set (0.00 sec) 

10. Example of INOUT usage in MySQL:

mysql> select 'ricksays' into @msg; Query OK, 1 row affected (0.00 sec)   mysql> delimiter // mysql> create procedure foobar (inout msg varchar(100)) -> begin -> set msg = concat(@msg, " never gonna let you down"); -> end//   mysql> delimiter ;   mysql> call foobar(@msg); Query OK, 0 rows affected (0.00 sec)   mysql> select @msg; +-----------------------------------+ | @msg                              | +-----------------------------------+ | ricksays never gonna let you down | +-----------------------------------+ 1 row in set (0.00 sec) 

Ok it worked, it joined the strings together. So you defined a variable msg, passed in that variable into stored procedure called foobar, and @msg was written to by foobar.

Now you know how to make stored procedures with delimiters. Continue this tutorial here, start in on variables within stored procedures: http://net.tutsplus.com/tutorials/an-introduction-to-stored-procedures/

Answer 2

Here is the sample MYSQL Stored Procedure with delimiter and how to call..

DELIMITER $$  DROP PROCEDURE IF EXISTS `sp_user_login` $$ CREATE DEFINER=`root`@`%` PROCEDURE `sp_user_login`(   IN loc_username VARCHAR(255),   IN loc_password VARCHAR(255) ) BEGIN    SELECT user_id,          user_name,          user_emailid,          user_profileimage,          last_update     FROM tbl_user    WHERE user_name = loc_username      AND password = loc_password      AND status = 1;  END $$  DELIMITER ; 

and call by, mysql_connection specification and

$loginCheck="call sp_user_login('".$username."','".$password."');"; 

it will return the result from the procedure.