CODEWITHSUNDEEP
mysqlstored-proceduresmysql-workbench
Rohit Kumar
Rohit Kumar

Reputation: 5

PREPARE Stmt giving syntax Error in stored procedure

I do not understand what have I done wrong.

I have a stored procedure in SQL Server and I need to migrate to MySQL.

When I run same query which I printed is working, but the same variable is not able to make prepare statement, resulting in syntax error.

Error Code: 1064 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 'if exists (select * from INFORMATION_SCHEMA.TABLES where table_name='bkup2_t_amt' at line 1

DELIMITER //
DROP PROCEDURE IF EXISTS pull_amts_from_PS;

CREATE  Procedure pull_amts_from_PS ( p_PSEnv VARCHAR(50))
BEGIN
    DECLARE v_cmd NVARCHAR (3500);
    #-- --------------  backup previous amts records
    DECLARE v_seq int;
    DECLARE v_iterations int;

    SET v_iterations = 10;

    SELECT info_fieldval_int into @seq from t_lrdb_general_info WHERE info_fieldname = 'amts_backup_sequence_no' ;
    select concat('SEQ #: ',@seq) ;

    select concat(concat('bkup',@seq),'_t_amts_input') into @v_cmdTemp;
    select concat('v_cmdTemp #: ',@v_cmdTemp) ;


    set @seq=concat("select * from INFORMATION_SCHEMA.TABLES where table_name='",@v_cmdTemp,"' and table_type='BASE TABLE'");



    select concat('SEQ 1 #: ',@seq) ;

    set @v_cmdTemp1=concat(  "if exists (", @seq ,") THEN  DROP TABLE ", @v_cmdTemp ," ; END IF;");

   select concat('v_cmdTemp1 1 #: ',@v_cmdTemp1) ;


    PREPARE stmt FROM @v_cmdTemp1;
    EXECUTE stmt ;
    DEALLOCATE PREPARE stmt ;

END;
//
DELIMITER ;

output :-

SEQ #: 2

v_cmdTemp #: bkup2_t_amts_input

SEQ 1 #: select * from INFORMATION_SCHEMA.TABLES where table_name='bkup2_t_amts_input' and table_type='BASE TABLE'

v_cmdTemp1 1 #: if exists (select * from INFORMATION_SCHEMA.TABLES where table_name='bkup2_t_amts_input' and table_type='BASE TABLE') THEN  DROP TABLE bkup2_t_amts_input ; END IF;

Last value of v_cmdTemp1 , when execute it work fine , but with variable execution it does not work.

Upvotes: 0

Views: 518

Answers (1)

Mike Lischke
Mike Lischke

Reputation: 53417

if exists is an ammendment in MySQL, not a primary command, hence you cannot write:

if exists drop table a;

Instead use the form:

drop table if exists a;

or for multiple tables:

drop tables if exists a, b, c;

You don't need to get anything from the I_S for the drop operation. You know the table's name, so just drop it.

Upvotes: 0

Related Questions