Dinidu Dinidu - 1 year ago 54
MySQL Question

Can not call a Mysql Stored Procedure more than one time, exists in the same sql file

My Stored Procedure is

CREATE Add_Template(
template_id_ VARCHAR(20),
file_name_ VARCHAR(100),
template_description_ VARCHAR(500))
INSERT INTO application_template_tab VALUES(template_id_,file_name_,template_description_);

I call this procedure as follow in the same .sql file

call Add_Template('ICT','frmIct.jsp','Information Communication Technology');
call Add_Template('Bac_Bus_Adm','frmBusAdm.jsp','Bachelor of Business Administration');

  1. Having these procedure callings in a separate file and execute it after executing the stored procedure also works fine.

  2. Having only one call after the stored procedure also works fine

But in the same file after the stored procedure, When I add the second call, it gives me the following error,

Error code 1064, SQL state 42000: 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 'call
Add_Template('Bac_Bus_Adm','frmBusAdm.jsp','Bachel' at line 2

Note:- Table creation Script

CREATE TABLE IF NOT EXISTS application_template_tab(
app_template_id VARCHAR(20) NOT NULL,
form_name VARCHAR(100) NOT NULL,
template_desc VARCHAR(500) NOT NULL,
PRIMARY KEY (app_template_id));

Answer Source

In your file, you are changing the statement delimiter from the default value ; using DELIMITER //.

This is required for complex statements like procedure declarations to be handled correctly by the client.

But the error suggests you are not setting it back to the default.

After END // but before the CALL statements, you need this:

DELIMITER ;  -- note there is a space required before the ;