1
Can anybody help me in finding error in the following SP please,
(actullay i need to drop the tables which had been created six months ago
CREATE PROCEDURE TGNSX00.TAB_DROP_PROC(OUT OUT_SQLCODE integer)
LANGUAGE SQL MODIFIES SQL DATA
DYNAMIC RESULT SETS 1
P1: BEGIN
Declare v_stmt varchar(100);
Declare TBSC varchar(100);
Declare TBNM varchar(100);
Declare SQLCODE integer default 0;
Declare C1 cursor WITH RETURN FOR
Select TABSCHEMA, TABNAME from DWGNS.TEMP_SRK_TABLE
WHERE (DAYS(CURRENT_TIMESTAMP)-DAYS(create_time))/30>6;
Open C1;
Fetch C1 into TBSC, TBNM;
While (SQLCODE=0) Do
Fetch C1 into TBSC,TBNM;
set v_stmt = 'DROP TABLE '|| rtrim(TBSC)||'.'||TBNM;
prepare v_stmt from v_stmt ;
execute v_stmt;
End While;
Close C1;
VALUES (SQLCODE) INTO OUT_SQLCODE;
P1: End@
