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?

Note that your syntax is good, this error only means the procedure was already created (you run the script for the 2nd time). Drop it first: `DROP PROCEDURE addfields;` after doing the `USE` and before doing the `DELIMITER` (since I've used `;`, it would be after the `DELIMITER` and before `CREATE PROCEDURE` if you use `DROP PROCEDURE addfields$$`) — Xenos, Oct 11 '19 at 08:41

score 74 · Answer 1 · edited Jul 14 '20 at 20:53

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/

This is brilliant. Thank you. This should be wiki'd – pspahn Oct 16 '14 at 19:12 — pspahn, Oct 16 '14 at 19:12
Amazing explanation. Thanks a lot :-) – Ravi Hirani Jul 18 '16 at 13:04 — Ravi Hirani, Jul 18 '16 at 13:04

score 62 · Accepted Answer · answered Apr 03 '13 at 11:53

62

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.

answered Apr 03 '13 at 11:53

MKV

913
7
6

what it returns? _it returns any value **or** returns true / false._ – Phoenix Oct 14 '14 at 07:24
I have a question: why do some statements end with `;` and others end with the DELIMITER `$$`. If Delimiter denotes the end of a statement why wouldn't they all be `$$` or even yet, why is the delimiter even necessary? Couldn't you just end statements with `;`. So confused. – Kellen Stuart Jul 09 '16 at 14:50
Changing the delimiter allows you to use the standard delimiter inside the proc declaration, otherwise mysql would terminate there. Once declared, you change it back. – inarilo Mar 30 '17 at 10:39
1

The space behind the last DELIMITER statement was required when running on mariadb, not sure about mysql. – Lanklaas Oct 09 '17 at 12:55
you can also do "create or replace procedure sp_user_login" instead of dropping it first. – David Welborn Jun 06 '18 at 22:13
@DavidWelborn - Unfortunately, the `create or replace procedure` syntax is not supported by MySQL. – ToolmakerSteve Jul 14 '20 at 21:21
WRT the question, the line `DROP PROCEDURE IF EXISTS your_procedure_name $$` is what was missing for OP. (NOTE: If this line was *before* `DELIMITER $$`, it would be `DROP PROCEDURE IF EXISTS your_procedure_name;`) – ToolmakerSteve Jul 14 '20 at 21:24
@ToolmakerSteve `create or replace procedure` is supported by versions >= 10.1.3. I guess it is is a later version than most folks are using, so good mention. – David Welborn Jul 22 '20 at 11:28

score 2 · Answer 3 · edited Apr 10 '17 at 15:33

Here is my code to create procedure in MySQL :

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `procedureName`(IN comId int)
BEGIN
select * from tableName 
         (add joins OR sub query as per your requirement)
         Where (where condition here)
END $$
DELIMITER ;

To call this procedure use this query :

call procedureName(); // without parameter
call procedureName(id,pid); // with parameter

Detail :

1) DEFINER : root is the user name and change it as per your username of mysql localhost is the host you can change it with ip address of the server if you are execute this query on hosting server.

Read here for more detail

score 0 · Answer 4 · answered Jul 19 '18 at 11:58

0

I have created a simple MySQL procedure as given below:

DELIMITER //
CREATE PROCEDURE GetAllListings()
 BEGIN
 SELECT nid, type, title  FROM node where type = 'lms_listing' order by nid desc;
END //
DELIMITER;

Kindly follow this. After the procedure created, you can see the same and execute it.

answered Jul 19 '18 at 11:58

Md Jamiruddin

17
2

This answer does not appear to add anything to the discussion, that was not already covered in earlier answers. When adding an answer *later*, please state what your answer shows, that is not already shown by existing answers. – ToolmakerSteve Jul 14 '20 at 21:16

score 0 · Answer 5 · answered May 29 '19 at 12:43

0

MY SQL STORED PROCEDURE CREATION

DELIMiTER $$
create procedure GetUserRolesEnabled(in UserId int)
Begin

select * from users
where id=UserId ;
END $$
DELIMITER ;

answered May 29 '19 at 12:43

Hari Lakkakula

199
1
4

This answer does not appear to add anything to the discussion, that was not already covered in earlier answers. When adding an answer *later*, please state what your answer shows, that is not already shown by existing answers. – ToolmakerSteve Jul 14 '20 at 21:16

MySQL create stored procedure syntax with delimiter

5 Answers5

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

MY SQL STORED PROCEDURE CREATION

Linked

Related