Procedure run time

Question

DELIMITER $ 
DROP PROCEDURE IF EXISTS CREATE_BACKUP$
CREATE PROCEDURE CREATE_BACKUP()
BEGIN 
DECLARE BACK INT DEFAULT 0;
SELECT TABLE_NAME 
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'STUDENTDB'
;

    SHOW_LOOP:LOOP

        IF BACK = 1 
        THEN  
        LEAVE SHOW_LOOP;
        END IF;

        CREATE TABLE STUDENT_BACKUP 
        AS SELECT * FROM STUDENT;
        CREATE TABLE SCORE_BACKUP 
        AS SELECT * FROM SCORE;
        CREATE TABLE GRADE_EVENT_BACKUP 
        AS SELECT * FROM grade_event;
    END LOOP SHOW_LOOP;

END$
DELIMITER ;

Hi, when I run this procedure, it runs more than one time. So I get an error which says "STUDENT_BACKUP table already exists" for the second time when it runs. What should I do to run it just 1 time?

what do you need the loop for? – RoMEoMusTDiE May 16 '17 at 02:15 — RoMEoMusTDiE, May 16 '17 at 02:15
Why is there a `LOOP` in there? – tadman May 16 '17 at 02:15 — tadman, May 16 '17 at 02:15

score 1 · Answer 1 · edited May 23 '17 at 12:34

1

In MySQL you can use CREATE TABLE IF NOT EXIST... to avoid the error occurrence. See CREATE TABLE syntax for details.
To solve your quesrion for SQL server use an INFORMATION_SCHEMA view. A similar solution is in the existing topic.

edited May 23 '17 at 12:34

Community

1
1

answered May 16 '17 at 02:23

Alexander

4,420
7
27
42

Procedure run time

1 Answers1