0

Now trying to create a new procedure as root user with some additional parameters

DELIMITER //
USE `user_main_table`;
DROP PROCEDURE IF EXISTS insert_user_temp;
CREATE DEFINER='root'@'localhost' PROCEDURE insert_user_temp()
 BEGIN 
  DECLARE row_coun_no INT;
  SELECT COUNT(`id`) INTO row_coun_no FROM user_temp;
  IF row_coun_no != 0 THEN
    TRUNCATE `user_data`;
    INSERT INTO user_data (employee_id,employee_code,employment_type,`emp_name`,first_name,middle_name,last_name,photo_file_name,email_id)
    SELECT employee_id,`employee_code`,`employment_type`,CONCAT(`first_name`," ",`last_name`),`first_name`,`middle_name`,`last_name`, `email_id`, `photo_file_name` FROM `user_temp`;
    TRUNCATE user_temp;
    UPDATE  `demo_user`.`tbl_users`, `user_main_table`.`user_data` SET tbl_users.profile_pic = user_data.photo_file_name
  END IF;
END;

DELIMITER ;

Executing above deletes the old procedure but does not create new with additional params defined

Running SP through phpmyadmin

enter image description here

UPDATED PROCEDURE

USE `user_main_table`;
DROP PROCEDURE IF EXISTS insert_user_temp;
CREATE DEFINER='root'@'localhost' PROCEDURE insert_user_temp()
 BEGIN 
  DECLARE row_coun_no INT;
  SELECT COUNT(`id`) INTO row_coun_no FROM user_temp;
  IF row_coun_no != 0 THEN
    TRUNCATE `user_data`;
    INSERT INTO user_data (employee_id,employee_code,employment_type,`emp_name`,first_name,middle_name,last_name,photo_file_name,email_id)
    SELECT employee_id,`employee_code`,`employment_type`,CONCAT(`first_name`," ",`last_name`),`first_name`,`middle_name`,`last_name`, `email_id`, `photo_file_name` FROM `user_temp`;
    TRUNCATE user_temp;
    UPDATE  `demo_user`.`tbl_users`, `user_main_table`.`user_data` SET tbl_users.profile_pic = user_data.photo_file_name
  END IF;
END;

Executing Procedure using mysql termial

CREATE PROCEDURE insert_user_temp_to_user()
BEGIN 
DECLARE row_coun_no INT;
SELECT COUNT(`id`) INTO row_coun_no FROM user_temp;
IF row_coun_no != 0 THEN
TRUNCATE `user_hrms`;
INSERT INTO user_hrms (employee_id,employee_code,employment_type,`emp_name`,first_name,middle_name,last_name,reporting_authority,department,designation,gender,location_name,branch_id,mobile_number,extension_number,email_id,`status`,`birthdate`,`anniversary`,`photo_file_name`,mtd,ytd,experience ,primary_skills,secondary_skills,joining_date,bu,award_category,award_category_desc)
SELECT employee_id,`employee_code`,`employment_type`,CONCAT(`first_name`," ",`last_name`),`first_name`,`middle_name`,`last_name`,`reporting_authority`,`department`,`designation`,`gender`,`location_name`,`branch_id`,`mobile_number`,`extension_number`,`email_id`,`status`,`birthdate`,`anniversary`,`photo_file_name`,mtd,ytd,experience,primary_skills,secondary_skills,joining_date,bu,award_category,award_category_desc FROM `user_temp`; 
TRUNCATE user_temp;
UPDATE `demo_intranet`.`tbl_users`, `intranet`.`user_hrms` SET tbl_users.profile_pic = user_hrms.photo_file_name
WHERE tbl_users.email = user_hrms.email_id;
END IF;
END;//
Query OK, 0 rows affected (0.02 sec)

If someone could help me understand what I am doing incorrectly here, I would greatly appreciate it. Thank you.

Slimshadddyyy
  • 4,085
  • 5
  • 59
  • 121

1 Answers1

2

Few issues with the syntax

  • You are missing the delimiter

  • The procedure name is missing braces ()

delimiter //
CREATE PROCEDURE insert_user_temp()
 BEGIN 
  DECLARE row_coun_no INT;
  SELECT COUNT(`id`) INTO row_coun_no FROM user_temp;
  IF row_coun_no != 0 THEN
    TRUNCATE `user_data`;
    INSERT INTO user_data (employee_id,employee_code,employment_type,`emp_name`,first_name,middle_name,last_name,photo_file_name,email_id)
    SELECT employee_id,`employee_code`,`employment_type`,CONCAT(`first_name`," ",`last_name`),`first_name`,`middle_name`,`last_name`, `email_id`, `photo_file_name` FROM `user_temp`;
    TRUNCATE user_temp;
    UPDATE  `demo_user`.`tbl_users`, `main_user`.`user_data` SET tbl_users.profile_pic = user_data.photo_file_name
    WHERE  tbl_users.email = user_data.email_id;
  END IF;
END;//

delimiter ;

Just tested in mysql terminal and it works for me

mysql> use test ;
mysql> delimiter //
mysql> CREATE PROCEDURE insert_user_temp()
    ->  BEGIN 
    ->   DECLARE row_coun_no INT;
    ->   SELECT COUNT(`id`) INTO row_coun_no FROM user_temp;
    ->   IF row_coun_no != 0 THEN
    ->     TRUNCATE `user_data`;
    ->     INSERT INTO user_data (employee_id,employee_code,employment_type,`emp_name`,first_name,middle_name,last_name,photo_file_name,email_id)
    ->     SELECT employee_id,`employee_code`,`employment_type`,CONCAT(`first_name`," ",`last_name`),`first_name`,`middle_name`,`last_name`, `email_id`, `photo_file_name` FROM `user_temp`;
    ->     TRUNCATE user_temp;
    ->     UPDATE  `demo_user`.`tbl_users`, `main_user`.`user_data` SET tbl_users.profile_pic = user_data.photo_file_name
    ->     WHERE  tbl_users.email = user_data.email_id;
    ->   END IF;
    -> END;//
Query OK, 0 rows affected (0.00 sec)

For PHPMyadmin you do not need the delimiter section in the code rather you can select them from the user interface.

Abhik Chakraborty
  • 44,654
  • 6
  • 52
  • 63
  • Being a newbie, I do not understand what exactly should be passed in delimiter ? – Slimshadddyyy Jun 24 '15 at 09:00
  • Check this answer http://stackoverflow.com/questions/29228490/why-delimiter-used-with-stored-procedure-in-mysql/29228588#29228588, so you can use `$$` or `//` for your delimiter as you wish. You choose something which may not appear in the query. – Abhik Chakraborty Jun 24 '15 at 09:01
  • Done with what you said, but now `Unknown table 'user_temp'` though it exists under DB `main_user` Is this related to sotred procedure error ? – Slimshadddyyy Jun 24 '15 at 09:04
  • Also check that the procedure is created under proper user, say you created a procedure as `root` and trying to remove this with some other user the procedure will not be deleted. – Abhik Chakraborty Jun 24 '15 at 09:04
  • Is `user_temp` is a temporary table ? Remember temporary tables exists for the current connection. – Abhik Chakraborty Jun 24 '15 at 09:06
  • User access is correct and trying to `Run SQL query/queries on database information_schema:` `user_temp ` is a table under DB `main_user` – Slimshadddyyy Jun 24 '15 at 09:06
  • Also can not see procedure deleted from `ROUTINES` table though I have tried deleting it – Slimshadddyyy Jun 24 '15 at 09:09
  • Just to make clear procedure is a part of database not table, run this command `SHOW PROCEDURE STATUS;` this will show the existing procedures and for which DB it is, try it as root user. – Abhik Chakraborty Jun 24 '15 at 09:11
  • @Slimshadddyyy: user_temp table should exists in same db where your procedure other wise add db also with table name in your query or use db before your statment. – Zafar Malik Jun 24 '15 at 09:31
  • @AbhikChakraborty given your inputs, I have modified my SP. Could you pls check it once as it deletes the old SP but does not create – Slimshadddyyy Jun 24 '15 at 09:35
  • @ZafarMalik, `user_temp` table exists in same DB – Slimshadddyyy Jun 24 '15 at 09:38
  • Are you running the the procedure create statement from mysql terminal or from some other tool like phpmyadmin ? if not running from terminal then you need to check the delimiter setup in those, I guess you can setup the delimiter there directly and then by removing it from the create part. – Abhik Chakraborty Jun 24 '15 at 10:08
  • @AbhikChakraborty, `Runing SQL query/queries on server "localhost": ` using phpmyadmin. Attached image from where it is being executed and checking SP details under `information_schema »ROUTINES` – Slimshadddyyy Jun 24 '15 at 10:13
  • Yes then you need to set the delimiter in the delimiter section and then remove `delimiter //` and `END;//` to `END;` and finally no need to have the last line `delimiter ;` In the delimiter section just add or select `//` or anything other than `;` – Abhik Chakraborty Jun 24 '15 at 10:14
  • @AbhikChakraborty pls see my updated SP as you mentioned. Error `CREATE DEFINER = `root`@`localhost` PROCEDURE insert_user_temp( ) BEGIN DECLARE row_coun_no INT;` How can I set delimiter ? – Slimshadddyyy Jun 24 '15 at 10:30
  • You need to remove backticks for root and localhost and should be as `CREATE DEFINER='root'@'localhost' PROCEDURE insert_user_temp()` – Abhik Chakraborty Jun 24 '15 at 10:35
  • @AbhikChakraborty, I updated to what you said but it still throws same exception. – Slimshadddyyy Jun 24 '15 at 10:52
  • Hmm its hard for me to test since I do not have phpmyadmin and I usually run on mysql terminal and I just ran the procedure and it works for me. Let me update that to the answer what I did. – Abhik Chakraborty Jun 24 '15 at 10:55