471,894 Members | 1,802 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,894 software developers and data experts.

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 3662
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
reply views Thread by YellowAndGreen | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.