469,283 Members | 2,297 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,283 developers. It's quick & easy.

stored procedure to drop tables

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@
Oct 23 '08 #1
1 3520
kazam
9
Can you give us error message details when you execute this procedure?
Oct 24 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by deprins | last post: by
7 posts views Thread by pkruti | last post: by
4 posts views Thread by Rhino | last post: by
8 posts views Thread by Thomasb | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.