CODEWITHSUNDEEP
stored-proceduresteradata
Vivek Harry
Vivek Harry

Reputation: 439

Execute Dynamic DDL statement in teradata stored procedure?

How to Execute Dynamic DDL statements IN TERADATA?

CREATE PROCEDURE DROP_INDEXES(IN indexs varchar(1000),IN p_database VARCHAR  (8000),IN p_table varchar(8000))
BEGIN
    DECLARE L_SQL VARCHAR(400);
    SET L_SQL= 'DROP INDEX '||trim(indexs)||' ON '||trim(db_name)|| '.'|| trim(tablename); 
    EXECUTE IMMEDIATE L_SQL;        
END ;

I need to call this child_procedure(DROP_INDEXES) from parent procedure, but during executing of the parent_procedure, after executing this procedure 

 CALL DROP_INDEXES(indexs,db_name,tablename);

automatically gets exit from the parent_procedure, the next statement is not executing from parent_procedure.

This is the error i'm getting:

Executed as Single statement.  Failed [3722 : HY000] SP_DROP_INDEXES:
Only a COMMIT WORK or null statement is legal after a DDL Statement. 
Elapsed time = 00:00:00.326

Kindly do help me regarding my issue.

Thanks in advance.

Upvotes: 0

Views: 2076

Answers (2)

access_granted
access_granted

Reputation: 1917

Try a different syntax:

REPLACE PROCEDURE DROP_INDEXES(IN indexs varchar(1000), IN p_database    VARCHAR  (8000),IN p_table varchar(8000))
BEGIN
    DECLARE L_SQL VARCHAR(400);
    SET L_SQL= 'DROP INDEX '||trim(indexs)||' ON '||trim(p_database)|| '.'|| trim(p_table); 
    --EXECUTE IMMEDIATE L_SQL;        
    CALL DBC.SysExecSQL( L_SQL );
END ;

REPLACE PROCEDURE PROC_TEST1 (IN db_name varchar(30),IN tablename varchar(30),IN indexs varchar(30))
BEGIN
  CALL DROP_INDEXES(indexs,db_name,tablename); 
  insert into test2 (charcol) select user; 
END;

call proc_test1(DATABASE,'test2','idx_test2');

Upvotes: 0

dnoeth
dnoeth

Reputation: 60482

In Teradata each DDL must be committed individually. Your session is running in ANSO mode, thus you need to add ;COMMIT; to the SQL string.

This should work:

SET L_SQL= 'DROP INDEX '||trim(indexs)||' ON '||trim(db_name)|| '.'|| trim(table name) || ';COMMIT;'

Upvotes: 1

Related Questions