[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Stored Procedure

Posted on 2011-10-24
4
Medium Priority
?
411 Views
Last Modified: 2012-05-12
How do I create a stored procedure in DB2 that will drop a table ?

Thanks
0
Comment
Question by:Los Angeles1
  • 2
4 Comments
 
LVL 18

Accepted Solution

by:
Dave Ford earned 668 total points
ID: 37019859

I suspect that there are more details to your question than the one sentence you specified, but the following might get you started:

CREATE procedure DropIt ()
  LANGUAGE SQL
  modifies sql data
BEGIN
   drop table deleteme cascade;
   commit;
END

Open in new window

0
 
LVL 37

Assisted Solution

by:momi_sabag
momi_sabag earned 668 total points
ID: 37019875
how about something like

CREATE PROCEDURE drop_table ( IN table_name varchar(128))
LANGUAGE SQL
MODIFIES SQL DATA

P1: BEGIN
-- Declare variables
DECLARE DUMMY INT;

-- Declare Cursor
DECLARE myCursor CURSOR  FOR
SELECT 1 x
FROM SYSCAT.tables
WHERE table_name = ?
WITH UR;

open myCursor using table_name;

fetch myCursor into dummy;

if (sqlcode = 0) then
 execute immediate 'drop table ' || table_name
end if

END P1
0
 
LVL 46

Assisted Solution

by:Kent Olsen
Kent Olsen earned 664 total points
ID: 37019961
Hi Greensburo,

I'm assuming that this question is a follow-up to your previous?  If so, you might consider moving the DROP TABLE statement back to the command prompt.  There's often a philosophical disagreement about running DDL from a stored procedure.

Run the DROP TABLE statement as a single command from the CLI before you run the rest of your script.

db2 'DROP TABLE sometable'

Unix shells can easily test the return value so that the script continues whether or not the DROP statement succeeded.  The same with DOS scripting, though the processes is a bit different.


Kent


0
 
LVL 18

Expert Comment

by:Dave Ford
ID: 37019976
Alternately,

CREATE PROCEDURE DropIt2 (IN p_tableName CHAR(100))
  LANGUAGE SQL MODIFIES SQL DATA
BEGIN
  DECLARE v_sql CHAR(256);
  SET v_sql = 'DROP table ' || trim(p_tableName) || ' cascade';
  PREPARE v_statement FROM v_sql;
  EXECUTE v_statement;
END

Open in new window

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Loops Section Overview
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question