CODEWITHSUNDEEP
phpmysqlsqlsql-server
Eric Santos
Eric Santos

Reputation: 203

error in mysql stored procedure

I am trying to create a stored procedure, checking if a table exists.

If it does, I want to drop it and create a new one. Otherwise, just create it.

Here's the code.

  DELIMITER \\
  CREATE PROCEDURE spCheckGraph (OUT var1 INT)  
    BEGIN
    DECLARE var0 INT;
    SELECT COUNT(*) INTO var1 FROM GRAPH_SUMMARY; 
       IF var1 =0 THEN
            CREATE TABLE GRAPH_SUMMARY(
            id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
            gender varchar(10),
            purpose_abroad_as_per_recorded_travel varchar(255),
            country_name varchar(255) 
        );
       ELSE
            Drop table GRAPH_SUMMARY;

            CREATE TABLE GRAPH_SUMMARY(
            id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
            gender varchar(10),
            purpose_abroad_as_per_recorded_travel varchar(255),
            country_name varchar(255) ;
       END IF;
    END \\
 DELIMITER ;

It throws this error:
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 '; END IF; END' at line 18

Upvotes: 1

Views: 140

Answers (1)

vee
vee

Reputation: 38645

The error is in your else block. You are missing closing parenthesis for the create table... statement.

Update your else block with: 

ELSE
  Drop table GRAPH_SUMMARY;

  CREATE TABLE GRAPH_SUMMARY(
    id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    gender varchar(10),
    purpose_abroad_as_per_recorded_travel varchar(255),
    country_name varchar(255)
  );
END IF;

Update:

To drop procedure if it already exists you'd do the following: 

DROP PROCEDURE IF EXISTS `spCheckGraph`;
DELIMITER \\
CREATE PROCEDURE spCheckGraph (OUT var1 INT)  
  BEGIN
    ...
  END;
\\
DELIMITER ;

Upvotes: 5

Related Questions