yevgeniy mordovkin yevgeniy mordovkin - 5 months ago 18
MySQL Question

MySQL create stored procedure syntax with delimiter

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?

MKV MKV
Answer

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.

Comments